You can hit ORA-00054: resource busy and acquire with NOWAIT specified error during some of your operations on your database such as alter some objects.

The main reason for this error is Table Level Locks(TM lock). The transaction holds two locks (Row Locks(TX) and Table Locks(TM) when the session opens. So What are 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
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       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
,  session_id

If you are on release 11g onward you can also use this syntax:

SQL>ALTER SESSION SET ddl_lock_timeout=100;

Session altered.

SQL>ALTER TABLE MY_TABLE MODIFY DEFAULT ATTRIBUTES TABLESPACE XXX;

 

Reklam

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Connecting to %s

%d blogcu bunu beğendi: