ORA-00054: resource busy and acquire with NOWAIT specified

Question

I killed a script that was in the middle of updating a table. Now when I rerun the script I am getting,

ORA-00054: resource busy and acquire with NOWAIT specified

I presume the table is locked?. How do I unlock the table?. Thanks in advance.

Answer 1

Step 1:

select object_name, s.sid, s.serial#, p.spid 
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

Step 2:

alter system kill session 'sid,serial#'; --`sid` and `serial#` get from step 1

More info: http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

Answer 2

Thanks for the info user 'user712934'

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;

Answer 3

You'll have to wait. The session that was killed was in the middle of a transaction and updated lots of records. These records have to be rollbacked and some background process is taking care of that. In the meantime you cannot modify the records that were touched.

Answer 4

When you killed the session, the session hangs around for a while in "KILLED" status while Oracle cleans up after it.

If you absolutely must, you can kill the OS process as well (look up v$process.spid), which would release any locks it was holding on to.

See this for more detailed info.