How to kill a process in an oracle deadlock

Source: Internet
Author: User

Step 1: Try to delete a process in sqlplus by running the SQL command. If the process can be deleted successfully, 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 Connected as quik SQL> select xidusn, object_id, session_id, locked_mode from v $ locked_object; -- check the deadlock object, 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 # USERNAME sid Based on the SID obtained in the previous step SERIAL # ------------------------------ ------------ QUIK 29 57107 SQL> alter system kill session '2017 07 '; -- delete a process. If the process has been deleted, a ora-00031 error is reported; otherwise, oracle will mark the session as killed and wait for a while to see if the session will automatically disappear. If the session will not disappear for a long time, after some ORACLE processes are killed, the status is set to "killed", but the locked resources are not released for a long time. Sometimes there is no way to restart the database. Now we provide a way to solve this problem, that is, it cannot be killed in ORACLE, and it can be killed again at the OS level. 1. the following statement is used to query which objects are locked: select object_name, machine, s. sid, s. serial # from v $ locked_object l, dba_objects o, v $ session s where l. object_id = o. object_id and l. session_id = s. sid; 2. the following statement is used to kill a process: alter system kill session '000000'; (24,111 is the sid found above, serial #) [note] the preceding two steps, it can be executed on the Oracle console. 3. if the process state is set to "killed" after a process is killed by using the preceding command, but the locked resources are not released for a long time, then, the corresponding process (thread) can be killed at the OS level. First, execute the following statement to obtain the process (thread) number: select spid, osuser, s. program from v $ session s, v $ process p where s. paddr = p. addr and s. sid = 24 (24 is the sid above) 4. killing the process (thread) on the OS: 1) on unix, run the command as root: # kill-9 12345 (that is, spid obtained in step 1) 2) in windows (unix also applies), use orakill to kill a thread. orakill is an executable Command provided by oracle. Syntax: orakill sid thread
Sid: indicates the Instance name of the process to be killed. thread: indicates the thread number to be killed, that is, the spid obtained in step 1. Example: c:> orakill orcl 12345

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.