在Oracle 8x中實(shí)現(xiàn)自動(dòng)斷開后再連接

在實(shí)際的數(shù)據(jù)庫應(yīng)用中，我們經(jīng)常遇到這樣一個(gè)問題，連接到Oracle數(shù)據(jù)庫的用戶在作了一次操作后，再也沒有后續(xù)操作，但卻長(zhǎng)時(shí)間沒有和數(shù)據(jù)庫斷開連接。對(duì)于一個(gè)小型的應(yīng)用系統(tǒng)來講，本身的連接數(shù)目就有限，這好像沒有什么嚴(yán)重的后果，但如果對(duì)于一個(gè)大型的數(shù)據(jù)庫應(yīng)用。如稅務(wù)、工商等，如果數(shù)據(jù)庫的連接數(shù)目很多，對(duì)于數(shù)據(jù)庫服務(wù)器來講，多一個(gè)連接就要多消耗一份資源，如果大量用戶連接進(jìn)入數(shù)據(jù)庫系統(tǒng)但卻不進(jìn)行任何的操作，這無形之中就白白造成了服務(wù)器系統(tǒng)資源的浪費(fèi)，同時(shí)造成服務(wù)器負(fù)載的提高，對(duì)于那些確實(shí)在工作的用戶來講，就不能最大限度的利用服務(wù)器的資源，嚴(yán)重情況下可能造成系統(tǒng)性能的急劇下降。

針對(duì)這種問題，該如何處理呢？對(duì)于目前流行的三層結(jié)構(gòu)（Browser/Application/Server）開發(fā)來講，這個(gè)問題可以通過設(shè)置應(yīng)用服務(wù)器端的連接共享池（shared pool）來避免。但對(duì)于傳統(tǒng)的兩層結(jié)構(gòu)（Client/Server）應(yīng)用，就必須由我們?nèi)藶楦深A(yù)來避免這種資源浪費(fèi)情況。具體可以通過一個(gè)后臺(tái)任務(wù)來監(jiān)控系統(tǒng)中的所有進(jìn)程，對(duì)于那些空閑超過一定時(shí)間的進(jìn)程采取一定的特殊處理措施，如在客戶端提示用戶連接時(shí)間太長(zhǎng)，如果沒有后續(xù)操作系統(tǒng)將自動(dòng)殺掉該連接或者直接將該空閑連接殺掉。下面就來具體討論如何在Oracle 8x環(huán)境下實(shí)現(xiàn)用戶進(jìn)程的自動(dòng)監(jiān)控及其對(duì)對(duì)超過一定空閑時(shí)間連接的處理辦法。

一、識(shí)別系統(tǒng)中超過一定空閑時(shí)間的連接

要實(shí)現(xiàn)后臺(tái)任務(wù)自動(dòng)對(duì)超過一定時(shí)間空閑連接的處理，首先第一步工作就是要從所有與數(shù)據(jù)庫服務(wù)器的連接中識(shí)別出那些連接需要處理，也就是需要獲得與服務(wù)器連接的每個(gè)用戶的登陸時(shí)間及其最后一次操作后的空閑時(shí)間。在Oracle系統(tǒng)中，有一個(gè)動(dòng)態(tài)性能視圖v$session，該視圖保存著系統(tǒng)當(dāng)前連接的各種動(dòng)態(tài)信息。其中，有兩個(gè)字段LOGON_TIME和 LAST_CALL_ET可以得到上面的兩個(gè)答案。

l LOGON_TIME是一個(gè)日期型（Date）字段，為用戶登陸時(shí)間；

l LAST_CALL_ET是一個(gè)數(shù)字型（Number）字段，其含義是用戶最后一條語句執(zhí)行完畢后的時(shí)間，單位為秒。每次用戶執(zhí)行一個(gè)新的語句后，該字段復(fù)位為0，重新開始記數(shù)。我們可以通過該字段來獲得一個(gè)連接用戶最后一次操作數(shù)據(jù)庫后的空閑時(shí)間。

下面的SQL查詢語句可以得到與當(dāng)前數(shù)據(jù)庫連接的所有用戶的一些基本情況，如用戶名、狀態(tài)、連接機(jī)器的名稱，操作系統(tǒng)中用戶的名稱，UNIX系統(tǒng)的進(jìn)程號(hào)，在UNIX操作系統(tǒng)級(jí)斷開連接的語句，Oracle數(shù)據(jù)庫系統(tǒng)斷開連接的語句，登陸時(shí)間以及最后一次操作到現(xiàn)在的空閑時(shí)間等等。

SELECT s.username 用戶名稱, s.status 狀態(tài),s.machine 機(jī)器名稱,
    　　　　osuser 操作系統(tǒng)用戶名稱,spid UNIX進(jìn)程號(hào),
    'kill -9 '||spid UNIX級(jí)斷開連接,
    'alter system kill session ' ||''''||s.sid||',
  '||s.serial# || ''';' Oracle級(jí)斷開連接,
    TO_CHAR (logon_time, 'dd/mm/yyyy hh24:mi:ss') 登陸時(shí)間,
    last_call_et 空閑時(shí)間秒,
    TO_CHAR (TRUNC (last_call_et / 3600, 0))||' '||' HRS '||
    TO_CHAR (
    TRUNC ((last_call_et - TRUNC
   (last_call_et / 3600, 0) * 3600) / 60, 0)
    ) ||' MINS' 空閑時(shí)間小時(shí)分鐘,
    module 模塊
    FROM v$session s, v$process p
    WHERE TYPE = 'USER'
    AND p.addr = s.paddr
    AND status != 'KILLED'
    -- AND SUBSTR (machine, 1, 19) NOT IN ('機(jī)器名')
    AND last_call_et > 60 * 60 * 1
    -- 空閑時(shí)間超過1小時(shí)的連接
    ORDER BY last_call_et desc;

在上面的查詢中，我們可以通過SUBSTR (machine, 1, 19) NOT IN ('機(jī)器名')這個(gè)條件來屏蔽一些機(jī)器，這些機(jī)器可能需要運(yùn)行一些耗費(fèi)很長(zhǎng)時(shí)間的SQL語句或其他一些特殊情況的機(jī)器。屏蔽這些機(jī)器的原因就是在后面的后臺(tái)自動(dòng)識(shí)別及處理任務(wù)中對(duì)這些機(jī)器不作處理。

二、識(shí)別及斷開空閑用戶的存儲(chǔ)過程

上面的查詢語句可以得到系統(tǒng)中所有的連接用戶的一些基本情況，但是又如何來實(shí)現(xiàn)系統(tǒng)自動(dòng)判斷空閑超過一定時(shí)間的連接并將其自動(dòng)斷開呢？Oracle系統(tǒng)提供了一種稱之為后臺(tái)任務(wù)（Job）自動(dòng)處理的機(jī)制。我們可以編寫一個(gè)后臺(tái)任務(wù)來定時(shí)執(zhí)行，從而判斷是否存在這樣的用戶連接，如果存在，則通過后臺(tái)任務(wù)將其自動(dòng)斷開。

首先創(chuàng)建一個(gè)存儲(chǔ)過程來完成空閑一定時(shí)間用戶的識(shí)別和斷開工作，然后添加一個(gè)后臺(tái)任務(wù)來定時(shí)（根據(jù)空閑時(shí)間長(zhǎng)短來確定）執(zhí)行該過程，即可實(shí)現(xiàn)自動(dòng)斷開系統(tǒng)中空閑超過一定時(shí)間用戶的需求。

存儲(chǔ)過程p_monitor見下，其中參數(shù)an_nimutes為用戶輸入?yún)��?shù)，用來確定識(shí)別和斷開多長(zhǎng)空閑時(shí)間連接的用戶，單位為分鐘，默認(rèn)為60分鐘，也就是1小時(shí)。需要注意一點(diǎn)的是，該存儲(chǔ)過程，需要以sys用戶身份運(yùn)行。相應(yīng)，調(diào)用該存儲(chǔ)過程的后臺(tái)任務(wù)也需要以SYS身份來添加。

CREATE OR REPLACE PROCEDURE P_MONITOR(
    AN_MINUTES NUMBER DEFAULT 60)
    /**//*******************************************
    存儲(chǔ)過程用途：識(shí)別出系統(tǒng)中超過一定空閑連接時(shí)間(
    　 AS_MINUTES)的用戶，并將其kill掉參數(shù):
    　 AN_MINUTES 空閑時(shí)間數(shù)，單位為分鐘，默認(rèn)為60分鐘
    ********************************************/
    AS
    　v_Str VARCHAR2(100);
    　CURSOR C_users(v_minutes number) IS 　SELECT s.username,
    　s.status, s.machine, 'alter system kill session '
    　||''''||s.sid||','||s.serial# ||'''' operates
    　FROM v$session s, v$process p
    　WHERE TYPE = 'USER'
    　AND p.addr = s.paddr
    　AND status != 'KILLED'
    　-- AND SUBSTR (machine, 1, 19) NOT IN ('需要屏蔽不被處理的機(jī)器名')
    　AND last_call_et > v_minutes*60
    　ORDER BY last_call_et desc;
    BEGIN
    FOR T_users IN C_users(an_minutes) LOOP
    v_Str := T_USERS.OPERATES;
    EXECUTE IMMEDIATE v_str;
    END LOOP;
    END;
    /

三、后臺(tái)任務(wù)的定時(shí)執(zhí)行

最后，我們?yōu)橄到y(tǒng)添加一個(gè)定時(shí)任務(wù)，定時(shí)調(diào)用上面創(chuàng)建的存儲(chǔ)過程，即可完成系統(tǒng)自動(dòng)識(shí)別和處理空閑用戶的工作。

下面是一個(gè)實(shí)際調(diào)用的例子，在sys用戶下，首先添加一個(gè)任務(wù)，該任務(wù)每隔半小時(shí)運(yùn)行一次，每次均調(diào)用P_monitor存儲(chǔ)過程，找出系統(tǒng)中空閑時(shí)間超過1小時(shí)的連接，然后自動(dòng)斷開。

DECLARE
    jobno number;
    BEGIN
    DBMS_JOB.SUBMIT(
    job => jobno,
    what => 'p_monitor(60);',
    next_date => SYSDATE,
    interval => '/*1:Hr*/ sysdate + 30/1440); -- 每半小時(shí)運(yùn)行一次
    END;
    /