You can hit ORA-00054: resource busy and acquire with NOWAIT specified error during some of your operation on your database.
The main reason for this error is Table Level Locks(TM lock). Transaction hold two locks (Row Locks(TX) and Table Locks(TM) when session opens. So What is those locks?
Row Locks (TX):Row-level locks are primarily used to prevent two transactions from modifying the same row.When a transaction needs to modify a row, a row lock is acquired.
Table Locks (TM): Table-level locks are primarily used to do concurrency control with concurrent DDL operations, such as preventing a table from being dropped in the middle of a DML operation. When a DDL or DML statement is on a table, a table lock is acquired. Table locks do not affect concurrency of DML operations. For partitioned tables, table locks can be acquired at both the table and the subpartition level.
When you hit this error you can try to run delete command for your issue.
You can also use below syntax to can find details about locks and then can kill session which keeps your object as locked:
Select oracle_username || ‘ (‘ || s.osuser || ‘)’ username
, s.sid || ‘,’ || s.serial# sess_id
, owner || ‘.’ || object_name object
, decode( l.block
, 0, ‘Not Blocking’
, 1, ‘Blocking’
, 2, ‘Global’) status
, 0, ‘None’
, 1, ‘Null’
, 2, ‘Row-S (SS)’
, 3, ‘Row-X (SX)’
, 4, ‘Share’
, 5, ‘S/Row-X (SSX)’
, 6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
Bir Cevap Yazın