Checking table locks in oracle database

TABLE LOCKS
No Wait
select
  object_name,
  object_type,
  session_id,
  type, -- Type or system/user lock
  lmode, -- lock mode in which session holds lock
  request,
  block,
  ctime -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name

LIBRARY LOCKS (Needs SYSDBA)
select
distinct
   ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,KSUSEMNM module,
   ob.kglnaown obj_owner, ob.kglnaobj obj_name
   ,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req
   , w.state, w.event, w.wait_Time, w.seconds_in_Wait
from
  x$kgllk lk, x$kglob ob,x$ksuse ses
  , v$session_wait w
where lk.kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0 )
and ob.kglhdadr = lk.kgllkhdl
and lk.kgllkuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc

Tables with resource busy wait
*****************************************

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

-- view all currently locked objects:
 
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;
 
 
-- list current locks
 
SELECT session_id,lock_type, 
mode_held, 
mode_requested, 
blocking_others, 
lock_id1
FROM dba_lock l
WHERE lock_type 
NOT IN ('Media Recovery', 'Redo Thread');
 
 
-- list 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;
 
 
-- alternate example:
 
SELECT s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name
object, k.kaddr, DECODE(l.locked_mode,
  1, 'No Lock',
  2, 'Row Share',
  3, 'Row Exclusive',
  4, 'Shared Table',
  5, 'Shared Row Exclusive',
  6, 'Exclusive') locked_mode,
  DECODE(k.TYPE,
    'BL','Buffer Cache Management (PCM lock)',
  'CF','Controlfile Transaction',
  'CI','Cross Instance Call',
  'CU','Bind Enqueue',
  'DF','Data File',
  'DL','Direct Loader',
  'DM','Database Mount',
  'DR','Distributed Recovery',
  'DX','Distributed Transaction',
  'FS','File Set',
  'IN','Instance Number',
  'IR','Instance Recovery',
  'IS','Instance State',
  'IV','Library Cache Invalidation',
  'JQ','Job Queue',
  'KK','Redo Log Kick',
  'LA','Library Cache Lock',
  'LB','Library Cache Lock',
  'LC','Library Cache Lock',
  'LD','Library Cache Lock',
  'LE','Library Cache Lock',
  'LF','Library Cache Lock',
  'LG','Library Cache Lock',
  'LH','Library Cache Lock',
  'LI','Library Cache Lock',
  'LJ','Library Cache Lock',
  'LK','Library Cache Lock',
  'LL','Library Cache Lock',
  'LM','Library Cache Lock',
  'LN','Library Cache Lock',
  'LO','Library Cache Lock',
  'LP','Library Cache Lock',
  'MM','Mount Definition',
  'MR','Media Recovery',
  'NA','Library Cache Pin',
  'NB','Library Cache Pin',
  'NC','Library Cache Pin',
  'ND','Library Cache Pin',
  'NE','Library Cache Pin',
  'NF','Library Cache Pin',
  'NG','Library Cache Pin',
  'NH','Library Cache Pin',
  'NI','Library Cache Pin',
  'NJ','Library Cache Pin',
  'NK','Library Cache Pin',
  'NL','Library Cache Pin',
  'NM','Library Cache Pin',
  'NN','Library Cache Pin',
  'NO','Library Cache Pin',
  'NP','Library Cache Pin',
  'NQ','Library Cache Pin',
  'NR','Library Cache Pin',
  'NS','Library Cache Pin',
  'NT','Library Cache Pin',
  'NU','Library Cache Pin',
  'NV','Library Cache Pin',
  'NW','Library Cache Pin',
  'NX','Library Cache Pin',
  'NY','Library Cache Pin',
  'NZ','Library Cache Pin',
  'PF','Password File',
  'PI','Parallel Slaves',
  'PR','Process Startup',
  'PS','Parallel Slave Synchronization',
  'QA','Row Cache Lock',
  'QB','Row Cache Lock',
  'QC','Row Cache Lock',
  'QD','Row Cache Lock',
  'QE','Row Cache Lock',
  'QF','Row Cache Lock',
  'QG','Row Cache Lock',
  'QH','Row Cache Lock',
  'QI','Row Cache Lock',
  'QJ','Row Cache Lock',
  'QK','Row Cache Lock',
  'QL','Row Cache Lock',
  'QM','Row Cache Lock',
  'QN','Row Cache Lock',
  'QO','Row Cache Lock',
  'QP','Row Cache Lock',
  'QQ','Row Cache Lock',
  'QR','Row Cache Lock',
  'QS','Row Cache Lock',
  'QT','Row Cache Lock',
  'QU','Row Cache Lock',
  'QV','Row Cache Lock',
  'QW','Row Cache Lock',
  'QX','Row Cache Lock',
  'QY','Row Cache Lock',
  'QZ','Row Cache Lock',
  'RT','Redo Thread',
  'SC','System Commit number',
  'SM','SMON synchronization',
  'SN','Sequence Number',
  'SQ','Sequence Enqueue',
  'SR','Synchronous Replication',
  'SS','Sort Segment',
  'ST','Space Management Transaction',
  'SV','Sequence Number Value',
  'TA','Transaction Recovery',
  'TM','DML Enqueue',
  'TS','Table Space (or Temporary Segment)',
  'TT','Temporary Table',
  'TX','Transaction',
  'UL','User-defined Locks',
  'UN','User Name',
  'US','Undo segment Serialization',
  'WL','Writing redo Log',
  'XA','Instance Attribute Lock',
  'XI','Instance Registration Lock') TYPE
FROM gv$session s, sys.gv$lock c, sys.gv$locked_object l,
     dba_objects o, sys.gv$lock k, gv$lock v
WHERE o.object_id = l.object_id
AND l.session_id = s.sid
AND k.sid = s.sid
AND s.saddr = c.saddr
AND k.kaddr = c.kaddr
AND k.kaddr = v.kaddr
AND v.saddr = s.saddr
AND k.lmode = l.locked_mode
AND k.lmode = c.lmode
AND k.request = c.request
ORDER BY object;