ORA-00054: the resource is busy, but specifies to get the resource in NOWAIT mode, or timeout is invalid

Quot; ORA-00054: the resource is busy, but specifies to get the resource in NOWAIT mode, or timeout failure quot; quick solution today in the export of a temporary table data, after exporting

Quot; ORA-00054: the resource is busy, but specifies to get the resource in NOWAIT mode, or timeout failure quot; quick solution today in the export of a temporary table data, after exporting

"ORA-00054: resources are busy, but specify the way to get resources in NOWAIT, or timeout failure" quick solution today in the export of a temporary table data, after the export is complete, prepare to clear the data, when executing the truncate command, the following problems are encountered:

ORA-00054: the resource is busy, but specifies to get the resource in NOWAIT mode, or timeout is invalid

The solution is as follows:

SQL> select session_id from v $ locked_object;

SESSION_ID
----------
56

SQL & gt; SELECT sid, serial #, username, oSUSEr FROM v $ session where sid = 142;

Sid serial # USERNAME OSUSER
--------------------------------------------------------------------------------
56 2088 ghb fy

SQL> ALTER SYSTEM KILL SESSION '2017 8 ';

System altered

After executing the preceding command, the system prompts that the session is disconnected. Reconnect to the database and perform the truncate operation!

The principles are as follows:

Oracle Database lock type

Oracle Database locks can be divided into the following categories based on different protected objects: DML locks (data locks), used to protect data integrity; DDL locks (dictionary locks, dictionary locks) are used to protect the structure of database objects, such as schema definitions for tables and indexes. internal locks and latches protect the internal structure of the database.

The purpose of the DML lock is to ensure data integrity in the case of concurrency ,. In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes the DML statement, the system automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, the system automatically applies for the TX lock and places the lock flag of the actually locked data row. In this way, when the consistency of the TX lock is checked before the transaction locks, the lock mark does not need to be checked row by row. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, SX, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks.

Only the X lock (exclusive lock) exists on the Data row ). In the Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time. The lock is kept until the transaction is committed or rolled back. When two or more sessions execute the DML statement on the same record of the table, the first session locks the record, and other sessions are waiting. After the first session is submitted, the TX lock is released before other sessions can be locked.

When the Oracle database has a TX lock wait, if not timely processing will often cause the Oracle database to suspend, or cause the occurrence of a deadlock, produce ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transactions failed, etc.