Oracle鎖表了如何處理

[解決方案]

1)通過尋找已被鎖定的資料庫表以及相關的sid，serial#，spid；

select object_name,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;

 

2)在資料庫中殺死Session

alter system kill session 'sid,serial#'; --sid,serial#是上面查詢出來的結果；

 

3)殺死對應的應用程式

kill -9 spid

 

執行步驟示範：

[1] % sqlplus "/as sysdba"

 

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

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

Connected to:

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         LYJ       139     221    26402

 

SQL> !ps -ef|grep 26402

  oracle 27588  5389  1 10:29:37 pts/tc    0:00 /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    0:00 grep 26402

 

說明：根據上面查詢出來的sid,serial#，殺死Session；

SQL> alter system kill session '139,221';

 

System altered.

 

說明：可以看到Session殺死後，已經查詢不到鎖的對象；

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

 

說明：可以看到Session殺死後，進程依然存在；

SQL> !ps -ef|grep 26402

  oracle 27951 27949  0 10:30:49 pts/tc    0:00 grep 26402

  oracle 27949  5389  1 10:30:49 pts/tc    0:00 /usr/bin/csh -c ps -ef|grep 26402

  oracle 26402 26401  0 10:25:25 ?         0:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

 

說明：根據進程PID殺死進程；

SQL> !kill -9 26402

 

SQL> !ps -ef|grep 26402

  oracle 28110 28108  0 10:31:25 pts/tc    0:00 grep 26402

  oracle 28108  5389  1 10:31:25 pts/tc    0:00 /usr/bin/csh -c ps -ef|grep 26402