Who is locking this object?
Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
1) First find the sessions holding locks, and objects that are currently being locked.
The following SQL prompts you for a schema/owner name and lists all currently lock objects and sessions locking them:
select session_id,
oracle_username locker,
o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S’
, 3, ‘Row-X’
, 4, ‘Share’
, 5, ‘S/Row-X’
, 6, ‘Exclusive’) lock_mode
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
and o.owner like upper(’&SCHEMANAME’)
order by 2;
Or use the following SQL if you already know the name of the object that is being locked and you want to know the locking session:
select session_id,
oracle_username locker,
o.OBJECT_ID,object_name,object_type,process,DECODE (l.locked_mode
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S’
, 3, ‘Row-X’
, 4, ‘Share’
, 5, ‘S/Row-X’
, 6, ‘Exclusive’) lock_mode
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
and o.owner like upper(’&SCHEMANAME’)
and o.object_name like upper(’&object_name’)
order by 2;
2) Lookup detail about the sessions holding locks.
Now use the following SQL to see more detail about the locking session using session ID or SID you had listed from previous lock finding SQL:
select s.sid,p.spid os_pid,s.status, s.osuser,s.username,s.COMMAND,
s.MACHINE,s.MODULE,s.SERIAL#, s.SCHEMANAME, s.LOCKWAIT,s.action
from v$session s, v$process p
WHERE s.paddr = p.addr
and s.sid = &SESSION_ID;
3) Lookup the session’s current SQL.
Use the following SQL to the associated SQL using the SID you have found earilier:
select sa.sql_text txt
from v$session s,
v$sqlarea sa
where s.sql_address=sa.address(+)
and s.sql_hashvalue=sa.hash_value(+)
and s.sid = &SESSION_ID;