How to find Oracle Database Lock Waiters
strong>Related ORA errors:
If you get the following errors then this script, if used appropriately, would help you resolve ORA- errors such as the following:
ORA-02049: Timeout: distributed transaction waiting for lock
ORA-00056: DDL lock on object
ORA-00054: resource busy and acquire with NOWAIT
ORA-04022: Nowait requested, but had to wait to lock dictionary
ORA-04021: Timeout occurred while waiting to lock object
ORA-1591: Lock held by in-doubt distributed transaction
Bobby Akbari at idbasolutions.com.
Feb 2008.
strong>List Database Lock Waiters:
idbasolutions.com Apr 2008
set linesize 110
column osuser format a10
column username format a15
column CHEMANAME format a15
column COMMAND format a20
column MACHINE format a15
column MODULE format a10
select ‘lock waiter: ‘,s.sid,p.spid os_pid,s.PROCESS,s.status, s.osuser,s.username,
s.SCHEMANAME,s.COMMAND,
s.MACHINE,s.MODULE,s.SERIAL#, s.LOCKWAIT,s.action, s.type
from v$session s, v$process p
WHERE s.paddr = p.addr
and s.lockwait is not null
and s.SCHEMANAME=’&SCHEMA_NAME’
order by 4;
strong>The following is another way to look for locks:
SELECT * FROM V$SESSION_WAIT WHERE EVENT = ‘enqueue’;