How to check which objects are locked in Oracle database?
Sometimes its necessary to check which tables are locked on the database (be it by running ETL sessions or other database users). Information on current locks on the Oracle database objects can be found in the V$LOCKED_OBJECT view. Here’s a SQL query to check the locked objects on Oracle database:
SELECT a.session_id, a.oracle_username, a.os_user_name, b.owner, b.object_name, b.object_type, DECODE(a.locked_mode, 0, '0 - NONE: lock requested but not yet obtained', 1, '1 - NULL', 2, '2 - ROWS_S (SS): Row Share Lock', 3, '3 - ROW_X (SX): Row Exclusive Table Lock', 4, '4 - SHARE (S): Share Table Lock', 5, '5 - S/ROW-X (SSX): Share Row Exclusive Table Lock', 6, '6 - Exclusive (X): Exclusive Table Lock') LOCKED_MODE FROM v$locked_object a, dba_objects b WHERE a.object_id = b.object_id
In the above query I used DECODE function to display the locked mode descriptions instead of LOCKED_MODE numeric values which are otherwise somewhat cryptic.
The results are presented as below:
Reference on V$LOCKED_OBJECT view can be found in Oracle Database Online Documentation 12c at https://docs.oracle.com/database/121/REFRN/refrn30125.htm#REFRN30125