Troubleshoot Fix Resolve ORA-00054: resource busy and acquire with NOWAIT

Overview ORA-00054:
        This is a quite normal message and is reported if a SQL statement would have blocked waiting for another user to complete some operation. The SQL command with either:
  • Had a 'NOWAIT' clause so instead of blocking the statement returns this error.
  • Was DDL and was blocked.
DDL or SELECT with FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.

How to Check ORA-00054?
  • None normally required unless this is occurring when not expected.
      
  • Check v$lock for locks being held.

  • For DDL repeat the command. If the DDL is issued under DBMS_SQL it is possible to cause this error if the DDL touches an object related to the current PL/SQL block by some dependency.

  • For SELECT FOR UPDATE issue the same statement without the NOWAIT clause to see if the statement blocks (assuming the problem is reproducible). If it blocks check there is a blocker in v$lock to find the blocked process.
Query: select * from v$lock where request!=0;
select * from v$lock where type='TX' and id1='&1' and id2='&2'
                where &1 and &2 are the ID for the lock we are waiting on from above.
  • V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.
In order to see locked object query,

SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/

You can also query v$access and v$locked_object to see specific locks: 

select s.sid, s.serial#, p.spid
from
   v$session s,
   v$process p
where
   s.paddr = p.addr
and
   s.sid in (select SESSION_ID from v$locked_object);

How to Fix ORA-00054?

  • Re-execute the DDL after some time when the when the objects gets released from the lock.
  • If the task needs to be completed immediately, kill the sessions that are preventing the exclusive lock.
  • If it is bulk job which takes long time frame, get a maintenance window to prevent end user to connect to the database and then run the DDL.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google