Our application hangs all the time. many time it gives error
ORA 0054 resource busy.
How can I find which session has lock which table via which SQL. and what type lock it is ? I try to check in V$lock, but I do not understand all the field in V$lock. Does any body knows how to detect lock ?
in v$Lock which is Idb1, idb2, lmode etc..
Where will I get information of data dictnery tables.
How can I resolve lock ?????
The following query will identify users that are currently being locked in the system.
select b.username, b.serial#, d.idl1, 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;
The next query will identify users in the system that are causing the problems of locking.
select a.serial#, a.sid, a.username, b.idl1, 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;
I hope this helps, if not let me know and I will see what else I have in my bag of tricks!
Learning something new everyday
Forgetting something useful every minute!