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;

 

Advertisements
Advertisements
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: