The ultimate processing method of Oracle query deadlock and unlocking _oracle

Some Oracle processes are killed, the state is set to "killed", but the locked resources are not released for a long time, sometimes there is no way, had to restart the database. Now provides a way to solve this problem, which cannot be killed in Oracle, and then killed at OS level.

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

Copy Code code as follows:

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:

Copy Code code as follows:

Alter system kill session ' 24,111 ';

(24,111 of which are the above query sid,serial#)
The above two steps can be performed through Oracle's management console.

3. If you use the above command to kill a process, the process state is set to "killed", but the locked resource is not released for a long time, then you can kill the corresponding process (thread) at the OS level, first execute the following statement to obtain the process (thread) Number:

Copy Code code as follows:

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. Kill this process (thread) on the OS:

1 on UNIX, execute the command with root:
#kill-9 12345 (that is, the SPID in step 3rd)

2 to kill a thread with Orakill in Windows (Unix also applies), Orakill is an executable command provided by Oracle, syntax:
Orakill SID Thread

which
Sid: Represents the instance name of the process to kill
Thread: Is the number of threads to kill, that is, the 3rd step of the SPID query.
Example: C:>orakill ORCL 12345

Summary: Oracle sessions are locked often. But sometimes alter system kill session ' sid,serial# '; Can only be done by killing the corresponding process on the OS.