The perfect solution for locking oracle Table

Solution:

Ora-00031:session marked for kill handles locks that cannot be killed in oracle after some processes in ORACLE are killed, the status is set to "killed", but the locked resources are not released for a long time, and sometimes there is no choice but to restart the database. Now provide a way to solve this problem, that is, can not kill in the ORACLE, kill again at the OS level.

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

Select object_name,machine,s.sid,s.serial#from v$locked_object lmt dbathing objects o, v$session swhere l.object_id = o.object_id and l.session_id=s.sid

The following statement is used to kill a process:

Alter system kill session '24111; (of which 24111 are the sid,serial# queried above.

[note] the above two steps can be performed through the Oracle management console.

If the process state is set to "killed" after killing a process using the above command, but the locked resource has not been released for a long time, then you can kill the corresponding process (thread) at the os level, and first execute the following statement to get the process (thread) number:

Select spid, osuser, s.programfrom v$session s.programfrom v$session vain process pwhere s.paddr=p.addr and s.sid=24 (24 is the sid above)

Kill the process (thread) on OS: on unix, execute the command as root:

Kill-9 12345 (i.e. the spid queried in step 3)

-use orakill to kill threads in windows (unix is also applicable). Orakill is an executable command provided by oracle. The syntax is:

Orakill sid thread

Where:

Sid: indicates the instance name to which the process to be killed belongs

Thread: is the thread number to be killed, that is, the spid queried in step 3.

Example: C: > orakill orcl 12345

Summary

Query which objects are locked

Select object_name, machine, s.sid, s.serial#from v$locked_object l, dba_objects o, v$session swhere l.object_id = o.object_idand l.session_id = s.sid

The following statement is used to kill a process:

Alter system kill session '24111;-- (where 24111 is the sid,serial# queried above)

Get the process (thread) number

Select spid, osuser, s.PROGRAMfrom v$session s, v$process pwhere s.PADDR = p.ADDRand s.SID = '425'

Killing process

Kill-9 26099-spid queried

The above is the perfect solution for locking the oracle table introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!