Sometimes you can get ” ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ” error.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
When you run any DDL ( alter, drop, truncate ), LOCK TABLE or SELECT FOR UPDATE, you can get this error.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired Cause: Interested resource is busy. Action: Retry if necessary or increase timeout.
SQL> ALTER TABLE MSD.TEST_TABLE ADD (name varchar2(30)); ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
resource busy and acquire with NOWAIT specified or timeout expired
DDL_LOCK_TIMEOUT
To solve this problem, you can use DDL_LOCK_TIMEOUT parameter with related query as follows.
ALTER SESSION SET DDL_LOCK_TIMEOUT=6000; ALTER TABLE MSD.TEST_TABLE ADD (name varchar2(30));
or You can kill the blocking and locking sessions.
You can list which objects are holding and blocking the others with the following script.
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, SQ.SQL_FULLTEXT, S.LOGON_TIME FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;
If you get the same error even though you use DDL_LOCK_TIMEOUT parameter, then you should use the following query to find the related session which hold this table.
SELECT a.inst_id, status, username, machine, event, blocking_session, sql_id, last_call_et, status, 'alter system kill session ''' || a.sid || ',' || serial# || ''';' FROM gv$access a, gv$session b WHERE a.inst_id = b.inst_id AND a.sid = b.sid AND object = 'TEST_TABLE';
query result is as follows. You can run the following scripts to kill related sessions.
alter system kill session '489,46242,@5'; alter system kill session '824,41709,@4'; alter system kill session '877,15892,@7';
You can use the following query to find the blocking session according to related Schema.
SELECT c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.inst_id, b.status, b.osuser, b.machine,'alter system kill session ''' || b.sid || ',' || b.serial# || ',@'|| b.inst_id ||''';' FROM gv$locked_object a, gv$session b, dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id and c.owner= 'SCHEMA_NAME';
Once you kill the related sessions, you can alter the table as follows.
SQL> ALTER TABLE MSD.TEST_TABLE ADD (name varchar2(30)); Table altered.
Do you want to learn Oracle Database for Beginners, then Click and read the following articles.
Oracle Database Tutorials for Beginners ( Junior Oracle DBA )