select session_id “sid”,SERIAL# “Serial”,substr(OBJECT_NAME,1,20) “Object”,substr(OS_USER_NAME,1,10) “Terminal”,
substr(ORACLE_USERNAME,1,10) “Locker”,NVL(lockwait,’ACTIVE’) “Wait”,DECODE(LOCKED_MODE,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’, ‘UNKNOWN’) “Lockmode”,
OBJECT_TYPE “Type”
FROM SYS.gV_$LOCKED_OBJECT A,SYS.ALL_OBJECTS B,SYS.gV_$SESSION c
WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID ORDER BY 1 asc, 5 desc
SELECT SUBSTR(a.object,1,25) TABLENAME,
SUBSTR(s.username,1,15) USERNAME,
SUBSTR(p.pid,1,5) PID,
SUBSTR(p.spid,1,10) SYSTEM_ID,
DECODE(l.type,
‘RT’,’Redo Log Buffer’,
‘TD’,’Dictionary’,
‘TM’,’DML’,
‘TS’,’Temp Segments’,
‘TX’,’Transaction’,
‘UL’,’User’,
‘RW’,’Row Wait’,
l.type) LOCK_TYPE
FROM gv$access a, gv$process p, gv$session s, gv$lock l
WHERE s.sid = a.sid
AND s.paddr = p.addr
AND l.sid = p.pid
GROUP BY a.object, s.username, p.pid, l.type, p.spid
ORDER BY a.object, s.username;
select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from gv$locked_object a ,gv$session b,dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;