19 | 08 | 2017
Latest Articles
Popular Articles

Administration

ORA-00054 : resource busy and acquire with nowait specified

User Rating:  / 0
PoorBest 

ORA-00054 : resource busy and acquire with nowait specified

 

A few weeks ago I got a mail with the request to solve immediatly a lock in the database

 

alter table owner.table_t1 modify field1 varchar2(1000)

ORA-00054 : resource busy and acquire with nowait specified

 

I told our customer this is not a real error, the object table_t1 was probably hold in a shared locked mode by another session / transaction. The table is not locked but the metadata cannot be altered.

Let' s see what is going on

 

STEP 1 : what is the corresponding object id for the object our customer would like to change ?

 

DB_USER >select object_id from dba_objects where object_name='TABLE_T1';

OBJECT_ID
----------
48195

 

STEP 2 : Is there any lock currently hold  for that object ?

 

DB_USER >select session_id,oracle_username,locked_mode from v$locked_object where object_id=48195;

SESSION_ID ORACLE_USERNAME                LOCKED_MODE
----------------  ------------------------------             --------------------
3                        COCONUT                                      2

 

STEP 3 : Which is the session, which os user from which server ?

 

DB_USER >select sid,serial#,osuser,machine,terminal from v$session where sid=3;

SID    SERIAL# OSUSER
---------- ---------- ------------------------------
MACHINE
----------------------------------------------------------------
TERMINAL
----------------
3      45402        user-xyz-123
COMP-CORP\USERNAME
SERVERNAME

 

Hence we can kill that session, we can contact the user, stop the process or delay the table modification.

Optionally we can query v$transaction, to find the transaction ID (XID)

Note the above is normal. It is normal and expected behaviour the database does not allow we modify table metadata as long as there are active transactions which require those objects in (row) shared lock mode .