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

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