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

Question

Your table is already locked by some query. For example, you may have executed "select for update" and have not yet committed/rollbacked and fired another select query. Do a commit/rollback before executing your query.

from here ORA-00054: resource busy and acquire with NOWAIT specified

You can also look up the sql,username,machine,port information and get to the actual process which holds the connection

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$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;

Please Kill Oracle Session

Use below query to check active session info

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$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;

kill like

alter system kill session 'SID,SERIAL#';

(For example, alter system kill session '13,36543';)

Reference http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html

There is a very easy work around for this problem.

If you run a 10046 trace on your session (google this... too much to explain). You will see that before any DDL operation Oracle does the following:

LOCK TABLE 'TABLE_NAME' NO WAIT

So if another session has an open transaction you get an error. So the fix is... drum roll please. Issue your own lock before the DDL and leave out the 'NO WAIT'.

Special Note:

if you are doing splitting/dropping partitions oracle just locks the partition. -- so yo can just lock the partition subpartition.

So... The following steps fix the problem.

1. LOCK TABLE 'TABLE NAME'; -- you will 'wait' (developers call this hanging). until the session with the open transaction, commits. This is a queue. so there may be several sessions ahead of you. but you will NOT error out.
2. Execute DDL. Your DDL will then run a lock with the NO WAIT. However, your session has aquired the lock. So you are good.
3. DDL auto-commits. This frees the locks.

DML statements will 'wait' or as developers call it 'hang' while the table is locked.

I use this in code that runs from a job to drop partitions. It works fine. It is in a database that is constantly inserting at a rate of several hundred inserts/second. No errors.

if you are wondering. Doing this in 11g. I have done this in 10g before as well in the past.