Oracle exception ora-00054: Resource busy and acquire with Nowait specified

Today through toad Oracle database operations, encountered a strange exception information ora-00054: Resource busy and acquire with Nowait specified (resources are busy, need to specify Nowait), search for a long time, finally, find the corresponding solution, record it, authenticate it, and confirm it :)

When a database user inserts, updates, or deletes data from a table in a database, or adds a table's primary key, or the table's index, a ora-00054 often occurs: resource busy and acquire with Nowait specified errors.

If a transaction is being executed (or the transaction has been locked), the execution fails.

1. Users with DBA permissions can view database locks

 
SelectT2.username, t2.sid, t2.serial #, t2.logon _ timeFromV $ locked_object T1, V $ session T2WhereT1.session _ id=T2.sidOrder ByT2.logon _ time;
 

For example, testuser 339 13545 17:40:05 knows the Locked User testuser, Sid is 339, serial # Is 13545

2. view the specific SQL statement based on the SID. If the SQL statement is not important, kill

SelectSQL _textFromV $ session A, V $ sqltext_with_newlines BWhereDecode (A. SQL _hash_value,0, Prev_hash_value, SQL _hash_value)=B. hash_valueAndA. Sid= &SidOrder ByPiece;
 

 

The SQL statement, for example, begin: ID: = SYS. dbms_transaction.local_transaction_id; end;

3. Kill the transactionAlter system kill session '00000045 ';

4. You can execute other transaction SQL statements.

For example, add the table's primary key: alter table test add constraint pk_test primary key (test_no );

Introduction: http://space.itpub.net/12778571/viewspace-561543