Tuesday, November 17, 2015

[Oracle11g] Database Table Locked Out - ORA00054 - [SOLVED]

Issue: Unable to drop a Table in Oracle 11g. Error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.


Cause: The table object is locked out in the Oracle 11g Database.

Resolution: Killing the request serial# is the resolution to this issue and unlocking the Oracle Database table.

Find the locked object from the Oracle Database using the following query ,
select * from DBA_OBJECTS where OBJECT_NAME='TABLE_NAME'


Get the Object ID from this table

select * from V$LOCKED OBJECT where OBJECT_ID='OBJECT_ID_HERE';

From the above query get the SESSION_ID and run the following query to find the serial # for this session,


Now run the following query to kill the Session, the syntax is as below,

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';




Easy Solution:

Alternatively run the following query to find the SID and SERIAL # for the particular database object as seen in screenshot below,

SELECT OBJ.OBJECT_ID,OBJ.OBJECT_NAME, VSES.SID, VSES.SERIAL#
FROM V$LOCKED_OBJECT VLOCK,
V$SESSION VSES,
DBA_OBJECTS OBJ
WHERE
VLOCK.SESSION_ID = VSES.SID
AND VLOCK.OBJECT_ID = OBJ.OBJECT_ID
AND OBJ.OBJECT_NAME ='Your_Object/Table_Name_Here'

Now use the below query to kill session,

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

No comments:

Post a Comment