轉載於http://www.cnblogs.com/kerrycode/p/3636992.html

ORACLE資料庫會話有ACTIVE、INACTIVE、KILLED、 CACHED、SNIPED五種狀態。INACTIVE狀態的會話表示此會話處於非活動、空閒、等待狀態。例如PL/SQL Developer連線到資料庫，執行一條SQL語句後，如果不繼續執行SQL語句，那麼此會話就處於INACTIVE狀態。一般情況下,少量的INACTVIE會話對資料庫並沒有什麼影響，如果由於程式設計等某些原因導致資料庫出現大量的會話長時間處於INACTIVE狀態，那麼將會導致大量的系統資源被消耗，造成會話數超過系統session的最大值，出現ORA-00018:maximum number of sessions exceeded錯誤。

有時候需要清理那些長時間處於INACTIVE狀態的會話。人為定期檢查、殺掉這類會話肯定不太現實，要定期清理那些長時間處於INACTIVE的會話，只能透過作業來實現；另外需要注意，Kill掉這些會話需要需要謹慎，稍不注意，就有可能誤殺了一些正常的會話。那麼我們該如何定義這類會話呢？下面是我結合業務規則定義的：

1：會話的Status必須為INACTIVE，如果會話狀態為ACTIVE、KILLED、CACHED、SNIPED狀態，不做考慮。

2：會話必須已經長時間處於INACTIVE狀態。例如，處於INACTIVE狀態超過了兩小時的會話程式，才考慮Kill。這個視具體業務或需求決定，有可能超過半小時就可以殺掉會話程式。至於如何計算處於INACTIVE會話狀態的時間，這個可以透過V$SESSION的LAST_CALL_ET欄位來判別，需要查詢處於INACTIVE狀態兩小時或以上的會話，就可以透過查詢條件S.LAST_CALL_ET >= 60*60*2實現，當然最好寫成 S.LAST_CALL_ET >= 7200

3：連線到會話的程式。比如，某個特定的應用程式產生的INACTIVE會話才要清理。例如, Toad工具、PL/SQL Developer工具。關於PROGRAM這個需要根據當前專案的具體情況設定，下面僅僅使用TOAD.EXE、W3WP.EXE舉例說明。

SELECT SID, SERIAL#,MODULE, STATUS
FROM V$SESSION S
WHERE S.USERNAME IS NOT NULL
AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE')
AND S.LAST_CALL_ET >= 60*60*2
AND S.STATUS = 'INACTIVE'
ORDER BY SID DESC;

如果是RAC環境，那麼最好使用下面SQL語句，使用全域性檢視GV$SESSION。

SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE')
AND S.LAST_CALL_ET >= 2 * 60*60
AND S.STATUS = 'INACTIVE'
ORDER BY INST_ID DESC

接下來建立儲存過程SYS.DB_KILL_IDLE_CLIENTS. 方便呼叫該功能執行kill inactive 會話。注意：xxx部分用實際業務的PROGRAM來替代。

  
CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
job_no number;
num_of_kills number := 0;
BEGIN
  
FOR REC IN
(SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
AND UPPER(S.PROGRAM) IN ('xxx', 'xxx.EXE')   
AND S.LAST_CALL_ET >= 2*60*60                              
AND S.STATUS= 'INACTIVE'
ORDER BY INST_ID ASC
) LOOP
---------------------------------------------------------------------------
 -- kill inactive sessions immediately
---------------------------------------------------------------------------
 DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
execute immediate 'alter system kill session ''' || rec.sid || ', ' ||
rec.serial# || '''immediate' ;

DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
num_of_kills := num_of_kills + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of killed xxxx system sessions: ' || num_of_kills);
END DB_KILL_IDLE_CLIENTS;
/

另外，由於kill session是直接將session kill掉，有可能出現導致事物回滾的現象，其實我們可以使用disconnect session完成當前事務並終止session。這種方式比alter system kill session跟安全可靠。

CREATE OR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
job_no number;
num_of_kills number := 0;
BEGIN
  
FOR REC IN
(SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
AND UPPER(S.PROGRAM) IN ('xxxx', 'xxxx')   
AND S.LAST_CALL_ET >= 2*60*60                              
AND S.STATUS<>'KILLED'
ORDER BY INST_ID ASC
) LOOP
---------------------------------------------------------------------------
 -- kill inactive sessions immediately
---------------------------------------------------------------------------
 DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
execute immediate 'alter system disconnect session ''' || rec.sid || ', ' ||
rec.serial# || '''immediate' ;
  
DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
num_of_kills := num_of_kills + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of killed system sessions: ' || num_of_kills);
END DB_KILL_IDLE_CLIENTS;
/

然後，我們可以在作業（JOB）或Schedule裡面定期呼叫該儲存過程，也可以透過後臺作業結合shell指令碼實現定期清理空閒會話的功能。例如如下所示。

建立killSession.sh指令碼，呼叫該儲存過程SYS.DB_KILL_IDLE_CLIENTS

#!/bin/bash
  
  
  
logfile=/home/oracle/cron/session/log/killSession.log
  
echo " " >> $logfile 2>&1
echo "START ----`date`" >> $logfile 2>&1
sqlplus /nolog <<STATS
connect / as sysdba
exec sys.db_kill_idle_clients;
exit;
STATS
  
echo "END ------`date`" >> $logfile 2>&1

在crontab裡面配置後臺作業，每隔15分鐘執行一次，清理哪些滿足條件的空閒會話。

0,15,30,45 * * * * /home/oracle/cron/session/bin/killSession.sh >/dev/null 2>&1

oracle 定期清理inactive會話

相關文章