Printable View
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!
Will it capture lock occured by
for update nowait ??
you have many select for update right?
Programmers has wriiten all
select statment using for update nowait for all tables
so we are not able to capture lock.
many time we get resource busy ORA-0054.
System gets hangs for hours and hours
Doing update all the time in OLTP enviorment.
You need to seriously look at your application code and review it with the developers. For most part, Oracle default locking mechanisms are enough and you do not need so many locks for update nowait.
seriously agree with Raminder