Solution to Oracle deadlock

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

Step 2: Go to the operating system to delete the process. In this example, the operating system is IBM aix.

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ Administrator> telnet 10.73.52.7 -- remotely log on to the database server through telnet

AIX Version 5
(C) Copyrights by IBM and by others 1982,200 5.
Login: root -- enter the user name
Root's Password: -- enter the Password
**************************************** ***************************************
**
**
* Welcome to AIX Version 5.3! *
**
**
* Please see the README file in/usr/lpp/bos for information pertinent *
* This release of the AIX Operating System .*
**
**
**************************************** ***************************************
Last unsuccessful login: Fri Apr 23 14:42:57 BEIDT 2010 on/dev/pts/1 from 10.73
. 52.254
Last login: Fri Apr 23 15:27:50 BEIDT 2010 on/dev/pts/2 from 10.73.52.254

# Ps-ef | grep 2273286 -- View process details
Root 2289864 2494636 0 17:07:15 pts/1 grep 2273286
Oracle 2273286 1 0 14:38:24-oracleQUIK (LOCAL = NO)
# Kill-9 2273286 -- delete a process. perform this operation with caution. Do not write the wrong process number. If the key process of oracle is deleted, the database will crash!
# Ps-ef | grep 2273286 -- View again
Root 2289864 2494636 0 17:07:15 pts/1 grep 2273286

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill-9 spid