批次kill session實現指令碼

531968912發表於2016-01-19
Session指令碼

轉載在：%E6%89%B9%E9%87%8Fkill-session%E5%AE%9E%E7%8E%B0%E8%84%9A%E6%9C%AC.html

在很多使用,因為各種原因,我們需要定時批次的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掉,然後回滾事務.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-1979025/，如需轉載，請註明出處，否則將追究法律責任。

相關文章