在很多使用,因为各种原因,我们需要定时批量的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掉,然后回滚事务.