Specific solution for killing deadlocks in Oracle Processes

The problem we often encounter in actual operations is that in Oracle, the actual status has been set to "killed ", however, the process locks related system resources for a long time, but does not release it. We recommend a better way than restarting the database, that is, to forcibly kill those Oracle processes at the operating system level.

1. The following statement is used to query which objects are locked:

 
 

  
  
col object_name for a30  
  
  
col machine for a20  
  
  
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 '42,21993';  
 
 

(24,111 is the sid, serial # found above #)

You can use the following query to obtain statements similar to the preceding one in batches:

 
 

  
  
select 'alter system kill session ''' ||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;  
 
 

3. if the Oracle 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, the corresponding process thread can be killed at the OS level), and the process thread is obtained first) Number:

 
 

  
  
select spid, osuser, s.program   
  
  
from v$session s,v$process p  
  
  
where s.paddr=p.addr and s.sid=#sid;  
 
 

# Sid is the sid above)

4. Kill the corresponding process thread in the operating system ):

1) in Linux, run the command as root:

Kill-9 1234512345 is the spid obtained in step 1)

2) use orakill to kill a thread in windows. orakill is an executable Command provided by oracle. The syntax is orakill sid thread.

Where:

Sid: indicates the Instance name of the Oracle process to be killed.

Thread: the ID of the thread to be killed, that is, the spid obtained in step 1.

Example:

 
 

  
  
c:＞orakill orcl 12345  
 
 

You can write a stored procedure for Combined Query to automatically perform the preceding four steps to conveniently kill all Oracle processes that do not automatically release resources. However, this is generally not recommended, after all, using the root user to kill the process in the system is inherently risky!

Article by: http://database.csdn.net/c_oracle/tag/2