ORA-00054: resource busy and acquire with NOWAIT specified error

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
,  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

Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2078

Reklam


“ORA-00054: resource busy and acquire with NOWAIT specified error” için 2 cevap

  1. […] from: https://heliosguneserol.wordpress.com/2011/03/09/ora-00054-resource-busy-and-acquire-with-nowait-spec… Categories: Oracle Administration LikeBe the first to like this post. Şərhlər (0) Trackbacks (0) Şərh yazın Trackback […]

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 )

Facebook fotoğrafı

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

Connecting to %s

%d blogcu bunu beğendi: