Site icon IT Tutorial

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

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 )

Exit mobile version