How to Find Locks on Objects

ISSUE:
-----
ORA-00054: resource busy and acquire with NOWAIT specified

Locked sessions on Oracle Database Objects :-
-----------------------------------------------
Query 1:
-------
set pages 50000 lines 32767

SELECT s.inst_id,s.sid || ',' || s.serial# sess_id,
oracle_username || ' (' || s.osuser || ')' os_username,
owner || '.' || object_name,object_type,object_id,
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))  LOCK_MODE,
a.sql_text
FROM gv$locked_object v,
     dba_objects d,
     gv$lock l,
     gv$session s
     gv$sqlarea a
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;

Query 2: (More Information)
-------
set pages 50000 lines 32767

select
vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE,
         0,'NONE',
         1,'NULL',
         2,'ROW SHARE',
         3,'ROW EXCLUSIVE',
         4,'SHARE',
         5,'SHARE ROW EXCLUSIVE',
         6,'EXCLUSIVE', NULL) LOCK_MODE,
do.owner, do.object_name, do.object_type
, vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status, vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type, vs.logon_time, vs.last_call_et, vs.blocking_session_status,
vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time, vs.seconds_in_wait, vs.state
from gv$locked_object vlo
inner join dba_objects do on (vlo.object_id = do.object_id)
left outer join gv$session vs on (vlo.session_id = vs.sid);

Query 3:
-------
set pages 50000 lines 32767

SELECT
b.inst_id,a.session_id,b.serial#,b.STATUS,b.machine,a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE,
c.owner,c.object_name,c.object_type,c.object_id,d.sql_text
FROM
gv$locked_object a,
gv$session b,
dba_objects c,
gv$sqlarea d
WHERE b.sid     = a.session_id
AND a.object_id = c.object_id
ORDER BY a.oracle_username, a.session_id;

Query 4:
-------
set pages 50000 lines 32767

SELECT s.inst_id,OS_USER_NAME, ORACLE_USERNAME, s.sid, o.object_name,o.object_type, s.serial#, a.sql_text
FROM gv$locked_object l, dba_objects o, gv$session s, gv$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;


Command to kill the session:
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';

ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>';  (RAC)

Script to Kill all the locks
----------------------------
set pages 50000 lines 32767

SELECT 'ALTER SYSTEM KILL SESSION "'||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||"';'
AS "Statement to kill"
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;