kill oracle session脚本,批量kill session实现脚本 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+8...

在很多使用,因为各种原因,我们需要定时批量的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 <

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掉,然后回滚事务.