今天在Oracle上面跑一个存储过程,就是那个不断造数据的那个,因为循环的设置大了点,估计要等很久才能跑完,于是就想直接干掉这个进程,在网上找到如下杀掉Oracle死锁的东西:
Oracle杀死死锁进程
先查看哪些表被锁住了:
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
OWNER OBJECT_NAME SESSION_ID LOCKED_MODE ------------------------------ ----------------- WSSB SBDA_PSHPFTDT 22 3 WSSB_RTREPOS WB_RT_SERVICE_QUEUE_TAB 24 2 WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB 29 2 WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB 39 2 WSSB SBDA_PSDBDT 47 3 WSSB_RTREPOS WB_RT_AUDIT_DETAIL 47 3
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
USERNAME SID SERIAL# LOGON_TIME ------------------------------ ---------- ------- WSSB_RTACCESS 39 1178 2006-5-22 1 WSSB_RTACCESS 29 5497 2006-5-22 1
|
杀进程中的会话:
alter system kill session 'sid,serial#'; e.g alter system kill session '29,5497';
|
如果有ora-00031错误,则在后面加immediate;alter system kill session '29,5497' immediate;
我自己按照上面的做了,但是好像没有反应,于是就想直接把DB停掉,在sqlplus 下面shutdown immediate;
结果还是要等很久,不过所幸的是最后执行成功,再重启DB 数据都还好,这中间我看CPU的wait值很高,应该是在回滚刚才已经执行的命令,这里一定要耐心点 让DB跑完,不然很有可能导致数据库无法启动的情况,
下面总结在Oracle里面要杀掉一个process的做法(以下是在PL/SQL Developer里面运行):
select * from v$session;
这里的输出结果有一个栏位是PROCESS, 这个也是对应到系统的PID
alter system kill session 'SID,SERIAL#' immediate;
如果有必要可以直接在系统的层面kill掉进程ps -ef|grep PROCESS
kill -9 PROCESS
下面总结在DB2里面要杀掉一个process的做法(直接在user@hostname $ 命令提示符下运行):
db2 list application show detail
db2 force application
application-handle such as:
db2 "force application(543)"
The following example forces two users, with application-handle
values of 41408 and 55458, to disconnect from the database:
db2 "force application ( 41408, 55458 ) "
阅读(6606) | 评论(0) | 转发(0) |