Idle sessions holding locks.

Monitor Oracle database sessions

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,


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;

Latest Blog Items


Thank you for your interest in idbasolutions.

Contact us using this email: