The ultimate method of Oracle query deadlock and unlock


After some processes in Oracle are killed, the status is set to “killed”. However, the locked resources are not released for a long time. Sometimes there is no way to do so, so you have to restart the database. Now we provide a way to solve this problem, which is to kill those that cannot be killed in Oracle, and then kill them at the OS level.

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

Copy codeThe code is 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 codeThe code is as follows:
alter system kill session ‘24,111’;

(24111 are the Sid and serial ා, respectively
[note] the above two steps can be executed through Oracle’s management console.

3. If the process status is set to “killed” after using the above command to kill a process, but the locked resource has not been 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:

Copy codeThe code is 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 the process (thread) on OS:

1) On UNIX, execute the command as root:
#Kill – 9 12345 (the SPID found in step 3)

2) In windows (UNIX is also applicable), use orakill to kill the thread. Orakill is an executable command provided by Oracle. The syntax is as follows:
orakill sid thread

Among them:
Sid: indicates the instance name of the process to be killed
Thread: the thread number to be killed, that is, the SPID found in step 3.
Example: C: > orakill orcl 12345

Conclusion: Oracle sessions are often locked. But sometimes alter system kill session ‘SID, serial ා; does not completely kill the session. You can only kill the corresponding process on the OS.