朋友打电话,说遇到了oracle的锁问题,原因是开发人员用plsqldev对一张大表进行delete操作,由于执行很长时间还未结束,就强行断开,然后在执行truncate操作的时候报资源正忙错误。这个问题我之前也遇到过几次,处理方法也很简单,只要找到锁表的SESSION,KILL掉基本就可以执行truncate操作啦。为了总结这类的问题,我做了如下实验:
用plsqldev删除一张2000万行大约4G的表,操作一段时间后强行中断,然后执行truncate操作.
SQL> truncate table test01;
truncate table test01
*
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
可通过以下SQL查处是谁锁的表
SQL> Select
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
c.program,
c.osuser
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;
SID SERIAL# NAME OBJECT USERNA PROGRAM OSUSER
--- ---- ------ ------ ------ ------------ --------------------
19 166 DELETE TEST01 STREAM plsqldev.exe STREAMAdministrator
干掉这个SESSION
SQL> alter system kill session'19,166';
系统已更改。
再进行truncate操作
SQL> truncate table test01;
表被截断。
查看被干掉的SESSION状态
SQL> select sid,serial#,username,status from v$session where sid=148 and serial#=17;
SID SERIAL# USERNAME STATUS
---------- ---------- --------- --------
148 17 STREAM KILLED
此时,plsqldev已断开连接
此时SESSION的状态是KILLED,此时并没有回收这个SESSION的资源,(过一段时间后才会回收),如果要立即回收资源也很简单,找到这个SESSION在操作系统对应的进程号,在操作系统上直接干掉这个进程即可。
SQL> select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=125;
SPID OSUSER PROGRAM
------------------------ -------------------- ------------------------------
3708 STREAMAdministrator plsqldev.exe
Windows系统干掉oracle进程的命令是orakill 实例名 进程号
C:Documents and SettingsAdministrator>orakill stream 3708
Kill of thread id 3708 in instance stream successfully signalled.
linux系统和以上命令对应的命令是 kill -9 3708
再查看sid为125并且username为stream的session,资源已被回收。
SQL> select * from v$session where sid=125 and username='STREAM';
未选定行