SQL Query to Find Out the Locked Objects and Type of Lock

advertisements

_____________________________________________________________________________________________________________________

Here is the query to find the locked object, mode of locks etc. 

Providing 2 different queries. 

SQL Query

SELECT a.sid, a.serial#, a.username, c.os_user_name
 , a.program, a.logon_time, a.machine, a.terminal
 , b.object_id, substr(b.object_name,1,40) object_name
 , DECODE(c.locked_mode,1, 'No Lock',
                        2, 'Row Share',
                        3, 'Row Exclusive',
                        4, 'Shared Table',
                        5, 'Shared Row Exclusive',
                        6, 'Exclusive') locked_mode
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

Sample Output

USERNAME             SESS_ID    OBJECT                    OBJECT_TYPE             STATUS       MODE_HELD
-------------------- ---------- ------------------------- ----------------------- ------------ ----------
 (oracle)            5654,44423 SYS.SXXABC_ACCESS$        TABLE                   Global       Row-X (SX)
 (oracle)            5654,44423 SYS.SXXABC_ACCESS$        TABLE                   Global       Row-X (SX)
 (oracle)            5654,44423 SYS.SXXABC_ACCESS$        TABLE                   Global       Row-X (SX)

advertisements

 

SQL Query

col sess_serial for a15
col Object_ID_locked_mode for a35
col MAC_TERM for a45
SELECT a.sid|| ',' ||a.serial# sess_serial, a.username || ',' || c.os_user_name username
 , a.program, a.logon_time, a.machine || ',' || a.terminal mac_term
 , b.object_id|| ',' ||substr(b.object_name,1,40) || ',' ||
  DECODE(c.locked_mode,1, 'No Lock',
                        2, 'Row Share',
                        3, 'Row Exclusive',
                        4, 'Shared Table',
                        5, 'Shared Row Exclusive',
                        6, 'Exclusive') Object_ID_locked_mode
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

Sample Output

SESS_SERIAL     USERNAME             PROGRAM                                          LOGON_TIM MAC_TERM                                      OBJECT_ID_LOCKED_MODE
--------------- -------------------- ------------------------------------------------ --------- --------------------------------------------- -----------------------------------
5654,44423      ,oracle              oracle@proddc102123.madc2.trigger.com            19-SEP-21 proddc102123.madc2.trigger.com,UNKNOWN   520,SXVCS_ACCESS$,Row Exclusive

_____________________________________________________________________________________________________________________

Website Stats