How to deal with Oracle lock table

[Solution]

1) Search for the locked database table and related Sid, serial #, spid;

Select object_name,S. Sid, S. Serial #,P. spidFrom v $ locked_object L, dba_objects o, V $ session S, V $ PROCESS p where L. object_id = O. object_id and L. session_id = S. sid and S. paddr = P. ADDR;

 

2) Killing sessions in the database

Alter system kill session'Sid, serial #'; -- Sid, serial # is the result of the above query;

 

3) Kill the corresponding application

Kill-9Spid

 

Step-by-Step Demonstration:

[1] % sqlplus "/As sysdba"

 

SQL * Plus: Release 11.1.0.6.0-production on Fri Nov 26 09:10:32 2010

 

Copyright (c) 1982,200 7, Oracle. All rights reserved.

 

 

Connected:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production

With the partitioning and real application testing options

 

SQL>Select object_name, O. owner, S. sid, S. serial #, P. spid from V $ locked_object L, dba_objects o, V $ session S, V $ PROCESS p where L. object_id = O. object_id and L. session_id = S. sid and S. paddr = P. ADDR;

 

Object_name owner Sid serial # spid

----------------------------------------------

Exttest lyj139 22126402

 

SQL>! PS-Ef | grep 26402

Oracle 27588 5389 1 10:29:37 pts/TC/usr/bin/CSH-c ps-Ef | grep 26402

Oracle 26402 26401 0 10:25:25? 0: 00 oracleora11g (description = (local = yes) (address = (Protocol = beq )))

Oracle 27590 27588 0 10:29:37 pts/TC grep 26402

 

Note: Based on the above query resultsSid, serial #To kill the session;

SQL>Alter system kill session '123 ';

 

System altered.

 

Note: The lock object cannot be found after the session is killed;

SQL>Select object_name, O. owner, S. sid, S. serial #, P. spid from V $ locked_object L, dba_objects o, V $ session S, V $ PROCESS p where L. object_id = O. object_id and L. session_id = S. sid and S. paddr = P. ADDR;

 

No rows selected

 

Note: after the session is killed, the process still exists;

SQL>! PS-Ef | grep 26402

Oracle 27951 27949 0 10:30:49 pts/TC grep 26402

Oracle 27949 5389 1 10:30:49 pts/TC/usr/bin/CSH-c ps-Ef | grep 26402

Oracle 26402 26401 0 10:25:25? 0: 00 oracleora11g (description = (local = yes) (address = (Protocol = beq )))

 

Description: processes are killed based on the process PID;

SQL>! Kill-9 26402

 

SQL>! PS-Ef | grep 26402

Oracle 28110 28108 0 10:31:25 pts/TC grep 26402

Oracle 28108 5389 1 10:31:25 pts/TC/usr/bin/CSH-c ps-Ef | grep 26402