Idle sessions holding locks.
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 idle sessions locking them:
select s.username locker,s.status,s.sid,s.serial#,
to_char((s.last_call_et/60), ‘99999D99′) idle_mins,
object_name,object_type,s.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, v$session s
where l.object_id=o.object_id
and s.sid=l.session_id
and s.username=l.oracle_username
and s.status in (’INACTIVE’,'KILLED’)
and OWNER like upper(’&SCHEMA’)
order by 6;
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;