ORA : How to Release the lock in the table.

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

Many times we come across this ora error while trying to Drop,  Truncate, alter Table or  Create or Rebuild 

Index .There we need to analyze who has a lock on the table being dropped/truncated, or on the table whose index is being created/altered, and if it is valid. 

Then carry out below steps to get rid of that lock by killing the responsible session for it.

1. Connect to sqlplus using sys or system

2. select object_id from dba_objects where object_name='<tablename>'; 

3. select * from v$locked_object where object_id=<Object id found in point no.2>; 

Please Note the "oracle username" and "session_id". 

4. Or it can be done by querying  v$access 

select sid from v$access where owner='<table owner>' and object='<table name>'; 

Note session id number or "sid". 

5. select sid, serial#, command, taddr from v$session where sid=<session id number>; 

6. Now you have found the user and what they are doing. 

Investigation into the validity of this session needs to be made.

Many times it may be a job that ran before or a hanging query. If it is determined that this session needs to be terminated, 

go to step 7, or else wait until the user has completed the action. To find out what they are doing, look at the command number in the COMMAND column.

7. To terminate the session: 

alter system kill session '<session_id, serial_no>'; 

8. The session should now be killed and the lock should release.

Rechecking "v$locked_object" will tell you this. If the lock does not immediately release, there may be a rollback occuring. To check this, goto step nine, else dropping the table should work now. 

9. To check for rollback: 

select used_ublk from v$transaction where ADDR=<value from TADDR in v$session>; 

If there is a value there, this is the number of undo blocks used by the transaction. Wait one minute and again select "used_ublk" from  "v$transaction" where ADDR=<value from TADDR in v$session>;

Note the value. If it is decreasing, a rollback is occuring and based on the difference between these values, you can guesstimate the time required to complete the rollback. For example, if the first query returns a value of 

80000 and the second one returns 70000, it took 1 minute to rollback 10000 blocks. Based on this number, you can guestimate the time to complete the rollback. In this case, it would be 7 minutes. 

10. In some cases, you might need to kill the session at the OS level as well. Some 3rd party database monitoring software and certain unexplained situations will cause the lock to remain despite not showing up in 

"v$locked_object" after killing the session. 

Example :

-----Query to find object id for table------

select object_id from dba_objects where object_name='PSOPRDEFN';

37127

-----get sessions locking objects------

select * from v$locked_object where object_id=37127

XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME  OS_USER_NAME

---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------

PROCESS                  LOCKED_MODE

------------------------ -----------

        10          7    1665191      37127       1077 SYSADM                         sys_niraj

7608:2460                          3

         0          0          0      37127       1084 SYSADM                         psoft

1233114                            3

------get sessions-------

select sid, serial#, command, taddr from v$session where sid in (1077,1084);

    SID    SERIAL#    COMMAND TADDR

---------- ---------- ---------- ----------------

      1077       1401          6 0700000096C61080

      1084       1328          6 0700000096CB9E40

---kill locking sessions as below---

---alter system kill session 'sessionid,serial';

SQL> alter system kill session '1077,1401';

System altered.

SQL> alter system kill session '1084,1328';

System altered.