Query to check table lock in Oracle database.
GOAL

How do I know if an Oracle table is locked or not.

DEMO

DEMO: HOW TO FIND ALL SESSIONS HOLDING A TABLE LOCK

RELATED

HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE

SOLUTION

Following DBT_ROW_LOCKS query gives details of all locks.

E.g., the output of the query

USERNAME       SID SERIAL# PID    STATUS             BLOCKED    BLOCKED_BY STATUS_ELAP_SEC CONNECTED_FROM       PROGRAM_NAME         OBJECT_NAME LOCKED_ROWID       LOCKED_MODE LOCKED_MODE_DESC
---------- ------- ------- ------ ------------------ ---------- ---------- --------------- -------------------- -------------------- ----------- ------------------ ----------- ---------------------------------------------
DEMO           284   52279 30569  INACTIVE           NO                                292 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY                               3 ROW_X (SX): Row Exclusive Table Lock
DEMO            36   36732 30629  INACTIVE           NO                                211 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY                               3 ROW_X (SX): Row Exclusive Table Lock
DEMO             4   47113 30681  ACTIVE AND BLOCKED YES               284             154 dbpilot.vmware.local sqlplus@dbpilot.vmwa DEMO.DUMMY  AAAStsAAHAAAAFfAAA           3 ROW_X (SX): Row Exclusive Table Lock

Written At
08 MAY 202219:00
Red Hat Release
7.x x64
Database Release
19.13.0.0.0