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 .