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. It is mainly because a transaction is being executed (or the transaction has been locked), which leads to execution failure. 1. Users with DBA permission can view the database locks select t2.username, t2.sid, t2.serial #, t2.logon _ timefrom v $ locked_object T1, V $ session t2where t1.session _ id = t2.sid order by t2.logon _ time; for example, testuser 339 13545 17:40:05 knows the Locked User testuser, Sid is 339, serial # Is 135452. Check the specific SQL statement based on the SID. If the SQL statement is not important, you can use killselect SQL _text from V $ session A, V $ sqltext_with_newlines B where decode (. SQL _hash_value, 0, prev_hash_value, SQL _hash_value) = B. hash_value and. SID = & SID order by piece; The queried SQL statement, for example, begin: ID: = sys. dbms_transaction.local_transaction_id; end; 3. Kill the transaction alter system kill session '000000'; 4. You can execute other transaction SQL statements, such as adding the table's primary key: alter table test add constraint pk_test primary key (test_no );
Address: http://space.itpub.net/12778571/viewspace-561543