Oracle table deadlock handling ORA-00054

Try to delete sqlplus using SQL commands. If the deletion is successful, everything is fine! However, when a deadlock occurs

Try to delete sqlplus using SQL commands. If the deletion is successful, everything is fine! However, when a deadlock occurs

Step 1: Try to delete sqlplus using the SQL command. If the deletion is successful, everything is fine! However, if you want to delete a session with a deadlock through a command line or using an Oracle management tool, oracle will only mark the session as killed but cannot clear it, you often need to delete it at the operating system level through step 2!

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as quik

SQL> select xidusn, object_id, session_id, locked_mode from v $ locked_object; -- query the deadlock object and obtain its SESSION_ID

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
-----------------------------------------
10 30724 29 3
10 30649 29 3

SQL> select username, sid, serial # from v $ session where sid = 29; -- view its serial # number based on the sid obtained in the previous step.

Username sid serial #
--------------------------------------------------
QUIK 29 57107

SQL> alter system kill session '29,571 07 '; -- delete a process. If the process has been deleted, a ora-00031 error is reported; otherwise, oracle marks the session as killed, wait for a while to see if it will automatically disappear. If it cannot disappear for a long time, follow-up steps are required.

Alter system kill session '2017 07'

ORA-00031: session marked for kill

SQL> select pro. spid from v $ session ses, v $ process pro where ses. sid = 29 and ses. paddr = pro. addr; -- view the spid to delete a process according to the ID in the operating system.

SPID
------------
2273286