Monday 3 September 2007

ORA-00054: resource busy and acquire with NOWAIT specified

When You get an ORA-00054, it's usually impossible to do the DDL unless You spend hours inconveniencing end-users by nuking their sessions.

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy.
Action: Retry if necessary.

You have choices for avoiding the ORA-00054 error:

  • Re-run the change late at night when the database is idle.
  • Do all DDL during a maintenance window with all end-users locked-out.
  • Kill the sessions that are preventing the exclusive lock.
Oracle has several views for showing lock status, some of which show the username:
  • DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
  • DBA_DDL_LOCKS – Shows all DDL locks held or being requested
  • DBA_DML_LOCKS - Shows all DML locks held or being requested
  • DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
  • DBA_LOCKS - Shows all locks or latches held or being requested
  • DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks
The DBA_LOCK_INTERNAL view used to show locks for a specific user, and you can specify the query in the form:

SELECT
NVL(b.username,'SYS') username,
session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM
sys.dba_lock_internal a,
sys.v_$session b
where . . .

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);

You can simply kill the session if you're sure:
ALTER SYSTEM KILL SESSION ('sid,serial#');