How to Get Detail of Locks with Object Locked

SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

About these ads

2 thoughts on “How to Get Detail of Locks with Object Locked

  1. The query above give is very useful, we can come to know what are the object internally running, and id’s so that we can able to release lock which is required by another procedure

  2. Another version of query with SID and serial# details.

    set lines 100 pages 999
    col username format a20
    col sess_id format a10
    col object format a25
    col mode_held format a10
    select oracle_username || ‘ (‘ || s.osuser || ‘)’ username
    , s.sid || ‘,’ || s.serial# sess_id
    , owner || ‘.’ || object_name object
    , object_type
    , decode( l.block
    , 0, ‘Not Blocking’
    , 1, ‘Blocking’
    , 2, ‘Global’) status
    , decode(v.locked_mode
    , 0, ‘None’
    , 1, ‘Null’
    , 2, ‘Row-S (SS)’
    , 3, ‘Row-X (SX)’
    , 4, ‘Share’
    , 5, ‘S/Row-X (SSX)’
    , 6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
    from v$locked_object v
    , dba_objects d
    , v$lock l
    , v$session s
    where v.object_id = d.object_id
    and v.object_id = l.id1
    and v.session_id = s.sid
    order by oracle_username
    , session_id
    /

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s