Solving ora-00054 Oracle lock table problems, ora-00054oracle

Solving ora-00054 Oracle lock table problems, ora-00054oracle

1. run SQL: select session_id from v $ locked_object; check the Sessions of the lock table. There may be many sessions, but they are not normal.

2. SELECT sid, serial #, username, osuser FROM v $ session where sid = 162; replace 162 with the session_id you have found.

3. kill the session

Alter system kill session '2010 4'; 162,429 is the serial found in step 2 #,

Entire Process

Ora-00054: resources are busy, but specify how to get resources in nowait

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 DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. 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.
Pessimistic blockout and optimistic blockout
I. pessimistic Blocking
The lock takes effect before the user changes:
Select... for update (nowait)
Select * from tab1 for update
After the user issues this command, oracle will establish a row-level blocking for the data in the returned set to prevent modification by other users.
If other users perform dml or ddl operations on the data returned in the result set above, an error message or blocking will be returned.
1: update the returned result set or dele ...... the remaining full text>

Oracle lock table problems I have executed some delete operations cyclically in the background. How can I lock the table and unlock it?

What client does it submit after the Delete operation?
How can I call commit?