database locks

set echo on;
spool locking_issues.log;

— Identify users currently being locked in the system

select b.username, b.serial#, d.id1, a.sql_text
from v$session b, v$lock d, v$sqltext a
where b.lockwait = d.kaddr
and a.address = b.sql_address
and a.hash_value = b.sql_hash_value;

— Identify the user in the system that is causing the problem of locking the above user

select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;
spool off;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: