Friday, 22 May 2015

A simple way to overcome resource busy and acquire with NOWAIT ERROR

If a table is not committed properly in oracle , then Oracle DB will throw 

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

Solution: 

We need to wait for sometime until the locked session gets expired itself  
or by using the following command find the locked session and issue kill session command . 

(Note: SYSDBA Role is mandate)

select s.sid, s.serial#, p.spid  
from 
   v$session s, 
   v$process p 
where 
   s.paddr = p.addr 
and 
   S.sid in (select SESSION_ID from V$LOCKED_OBJECT);

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';


ALTER SYSTEM KILL SESSION '77,2568' IMMEDIATE;


SQL to get Session Details in Oracle:

SELECT s.inst_id,       s.sid,       s.serial#,       p.spid,       s.username,       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
       WHERE  s.type != 'BACKGROUND';

1 comment: