How to solve the ORA-00054: resourcebusyandacquirewithNOWAITspecified ?,

How to solve the ORA-00054: resourcebusyandacquirewithNOWAITspecified ?,

Yesterday's index came across ORA-00054: the problem of resource busy and acquire with NOWAIT specified, which took a lot of time to solve. This error is actually very simple. In this case, the user has not committed the transaction to this table, and thus cannot perform DDL operations on this table (index creation involves DDL operations ). Of course, the time spent is mainly on a pitfall. It is worth noting that the database is RAC. When I kill the session that the transaction has not committed, I cannot see the congestion, however, this error is still reported when you create an index because multiple nodes with multiple RAC servers need to be killed. Next, let's make a small experiment. It's just a single point of processing. RAC's processing is just a database that cannot be connected:

Session1: (manufacturing congestion, transactions are not committed)

SQL> create table tt as select * from dba_objects;

The table has been created.

SQL> update tt set object_id = object_id + 1;

49838 rows have been updated.

Session2: (index creation error)

SQL> create index ind_tt_object_id on tt (object_id );

Create index ind_tt_object_id on tt (object_id )*

Row 3 has an error:

ORA-00054: resource busy and acquire with NOWAIT specified

Session3: (identify which session has a problem, kill. If it is RAC, you need to connect to multiple databases kill)

SQL> select t2.username, t2.sid, t2.serial #, t2.logon _ time

From v $ locked_object t1, v $ session t2

Where t1.session _ id = t2.sid

Order by t2.logon _ time;

Username sid serial # LOGON_TIME

----------------------------------------------------------------

TEST 149 20845-4-month-14

SQL> alter system kill session '12345645 ';

The system has been changed.

Session2: (you can create another index)

SQL> create index ind_tt_object_id on tt (object_id );

The index has been created.