자동 세션 제거

자동 세션 제거

오라클에서 자체적으로 일정 간격으로 오래된 세션을 제거하는 Job을 생성해본다.

사용 중인 세션 조회

다음 SQL 구문을 이용하여 현재 사용 중인 세션 정보를 조회할 수 있다.

관리자 권한을 가지고 있어야 조회할 수 있다.

select sid, serial# from v$session;

일정 시간이 지난 세션에 대해 조회하고 싶을 경우 logon_time 항목을 조건으로 걸어서 조회할 수 있다.

select sid, serial#, logon_time from v$session;

조회한 값이 다음과 같다고 가정했을 때, 궁극적으로 실행해야 하는 구문은 다음과 같다.

sid	serial#
1	1
5	23
100	152

alter system kill session '1, 1';
alter system kill session '5, 23';
alter system kill session '100, 152';

일일이 작업할 수는 없기 때문에 다음과 같이 생성 구문을 만든 뒤 Procedure를 사용하여 실행하는 방법으로 진행한다.

select 'alter system kill session ''' || sid || ',' || serial# || '''' as ddl from v$session;

실행해보면 세션 종료 구문이 ddl이라는 이름으로 생성되는 것을 알 수 있다.

우리는 열린지 1시간이 지난 세션을 제거할 예정이므로 조건을 추가하여 다음과 같이 작성한다.

select 'alter system kill session ''' || sid || ',' || serial# || '''' as ddl from v$session
where logon_time < sysdate - (1/24);

SQL 구문 자동 실행

PL/SQL 을 이용하여 여러 개의 세션을 자동으로 종료할 수 있다. 단, 세션이 여러 이유로 종료되지 않을 수 있는데, 이 때는 아무런 작업을 하지 않도록 작성하였다.

-- session auto kill using PL/SQL
BEGIN
    -- 결과만큼 반복할 SQL 구문을 작성하고 별칭을 CUR로 부여
    FOR CUR IN (
        SELECT 
            'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || '''' 
                AS DDL 
            FROM V$SESSION
            WHERE LOGON_TIME < SYSDATE - (1/24)
    )
    LOOP
        BEGIN
            -- 조회된 구문 실행
            EXECUTE IMMEDIATE CUR.DDL;
        EXCEPTION
            -- 예외 발생 시 아무작업을 하지 않음
            WHEN OTHERS THEN
                NULL;
        END;  
    END LOOP;  
END;
/

실행했을 때 PL/SQL procedure successfully completed 라고 나오면 구문에 오류가 없는 것이다. 실행 완료된 구문을 프로시저(Procedure)로 만들기 위해 다음의 접두사를 추가한다.

CREATE OR REPLACE PROCEDURE AUTO_CLEAR_SESSION AS
--(작성한 PL/SQL 구문)

생성된 프로시저

생성된 프로시저 코드는 다음과 같다.

-- session auto kill procedure
CREATE OR REPLACE PROCEDURE AUTO_CLEAR_SESSION AS
BEGIN
    -- 결과만큼 반복할 SQL 구문을 작성하고 별칭을 CUR로 부여
    FOR CUR IN (
        SELECT 
            'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || '''' 
                AS DDL 
            FROM V$SESSION
            WHERE LOGON_TIME < SYSDATE - (1/24)
    )
    LOOP
        BEGIN
            -- 조회된 구문 실행
            EXECUTE IMMEDIATE CUR.DDL;
        EXCEPTION
            -- 예외 발생 시 아무작업을 하지 않
            WHEN OTHERS THEN
                NULL;
        END;  
    END LOOP;  
END;
/

자동 실행 설정

Oracle Job을 이용하여 주기적으로 실행되도록 처리할 수 있다.

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name => 'auto_clear_session_job',
        comments => 'kill old session longer than 1 hour',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN AUTO_CLEAR_SESSION; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
        enabled => TRUE
    );
END;
/

• job_name - job 이름

• comments - job 설명

• job_type - job 유형

• job_action - job이 실행할 코드

• start_date - 시작 시간

• repeat_interval - 반복 간격

• enabled - 사용 여부