List Database Lockers
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
List Database Lockers:
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 ‘locker: ‘,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.sid in
(select SESSION_ID from DBA_LOCKS
where BLOCKING_OTHERS <> ‘Not Blocking’)
order by 4;
Here is another way to look for lockers:
select * from DBA_LOCKS where BLOCKING_OTHERS <> ‘Not Blocking’