联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在很多使用,因为各种原因,我们需要定时批量的kill一部分session,用来释放数据库部分资源,这里是因为bug导致temp不能正常释放,也可能是因为bug导致pga不释放,还有可能是因为太多inactive占用资源等等.我这里提供了两种方法来实现该功能
存储过程实现kill session
--创建记录表 CREATE TABLE kill_session_record ( kill_time DATE, kill_statement VARCHAR2 (1000) ) / --创建kill session存储过程 CREATE OR REPLACE PROCEDURE kill_inactive_session IS CURSOR c IS SELECT sid, serial# FROM v$session s WHERE s.status = 'INACTIVE' AND s.username = 'XIFENFEI'; k_sid NUMBER; k_serial NUMBER; BEGIN OPEN c; FETCH c INTO k_sid, k_serial; WHILE c%FOUND LOOP BEGIN EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || k_sid || ',' || k_serial || ''' IMMEDIATE'; INSERT INTO kill_session_record (kill_time, kill_statement) VALUES ( SYSDATE, 'ALTER SYSTEM DISCONNECT SESSION ''' || k_sid || ',' || k_serial || ''' IMMEDIATE'); EXCEPTION WHEN OTHERS THEN INSERT INTO kill_session_record (kill_time, kill_statement) VALUES ( SYSDATE, 'Failure:ALTER SYSTEM DISCONNECT SESSION ''' || k_sid || ',' || k_serial || ''' IMMEDIATE'); COMMIT; END; FETCH c INTO k_sid, k_serial; END LOOP; COMMIT; CLOSE c; END; / --设置job定时运行 DECLARE job NUMBER; BEGIN sys.DBMS_JOB.submit (job, what => 'kill_inactive_session;', next_date => SYSDATE, interval => 'TRUNC(SYSDATE + 1) +7/24'); COMMIT; DBMS_OUTPUT.put_line (job); END; /
如果是10GR2之前版本,需要把ALTER SYSTEM DISCONNECT SESSION 换成ALTER SYSTEM KILL SESSION
shell kill session
--shell脚本 # more kill_inactive_session.sh #!/bin/sh tmpfile0=/tmp/.kill_inactive_0 tmpfile1=/tmp/.kill_inactive_1 tmpfile2=/tmp/.kill_inactive_2 sqlplus / as sysdba <<EOF spool $tmpfile1 select 'kill time:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') execute_time from dual; select p.spid,s.sid,s.serial# from v\$process p,v\$session s where s.paddr=p.addr and username='XIFENFEI' and s.status='INACTIVE'; spool off EOF cat $tmpfile1>>$tmpfile0 grep "^[0123456789]" $tmpfile1 |awk '{print $1}'>$tmpfile2 for x in `cat $tmpfile2` do kill -9 $x done rm $tmpfile1 $tmpfile2 --contab 调度 00 07 * * * /u01/script/kill_inactive_session.sh
两个脚本都可以在where中加一些限制条件,来实现你需要kill的会话.数据库级别kill相对系统级别来说更加温和点,建议优先考虑数据库级别kill session.如果要求立即释放资源,可能需要考虑系统级别.两中kill方式对于未提交且是inactive session都会被kill掉,然后回滚事务.
“ 换成ALTER SYSTEM KILL SESSION”,这个需要增加spid的内容
建议在数据库中操作的方法,要记录下来OS上的spid,否则如果出现session不在了,但是spid还在的情况,还要再处理很麻烦不说,也起不到作用啊…………