Tag Archives: objects

How to see list of objects that have been locked for 60 seconds or more

SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) “WAITING User”,
SUBSTR(s1.osuser,1,8) “OS User”,
SUBSTR(s1.program,1,20) “WAITING Program”,
s1.client_info “WAITING Client”,
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) “HOLDING User”,
SUBSTR(s2.osuser,1,8) “OS User”,
SUBSTR(s2.program,1,20) “HOLDING Program”,
s2.client_info “HOLDING Client”,
o.object_name “HOLDING Object”
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != ‘None’
AND h.mode_held != ‘Null’
AND w.mode_requested != ‘None’
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;

How to view all currently locked objects in database

SELECT username  U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
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 gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND  v.session_id = s.sid
ORDER BY username, session_id;

How to check the locked objects in the database

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,1,’NULL’,2,’ROW SHARE’,3,’ROW EXCLUSIVE’,4,’SHARE’,5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and    l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and    s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by  l.inst_id ;

select * from sys.GV_$LOCKED_OBJECT;