오라클 특정계정 모든 세션 죽이기

1. 프로시저
DECLARE
    PROCEDURE KILL_ALL_USER_SESSION_DBMS_SQL(P_USERNAME_SESSION IN VARCHAR2 := '')
    IS
    V_CURSOR_NAME_SESSION NUMBER := DBMS_SQL.OPEN_CURSOR;
    V_STMT_SESSION VARCHAR2(500) := 'SELECT SID AS SID, SERIAL# AS SERIAL
        FROM V$SESSION
        WHERE USERNAME IS NOT NULL AND USERNAME = '||''''||P_USERNAME_SESSION||'''';
    V_EXEC_SESSION NUMBER;
    V_SID_SESSION NUMBER;
    V_SERIAL_SESSION NUMBER;
    V_CURSOR_NAME_SESSION_KILL NUMBER;
    V_STMT_SESSION_KILL VARCHAR2(500);
    V_EXEC_SESSION_KILL NUMBER;
    BEGIN
        DBMS_SQL.PARSE(V_CURSOR_NAME_SESSION, V_STMT_SESSION, DBMS_SQL.NATIVE);
        V_EXEC_SESSION := DBMS_SQL.EXECUTE(V_CURSOR_NAME_SESSION);
        DBMS_SQL.DEFINE_COLUMN(V_CURSOR_NAME_SESSION,1,V_SID_SESSION);
        DBMS_SQL.DEFINE_COLUMN(V_CURSOR_NAME_SESSION,2,V_SERIAL_SESSION);
        LOOP
            EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR_NAME_SESSION) = 0;
            DBMS_SQL.COLUMN_VALUE(V_CURSOR_NAME_SESSION,1,V_SID_SESSION);
            DBMS_SQL.COLUMN_VALUE(V_CURSOR_NAME_SESSION,2,V_SERIAL_SESSION);
            V_STMT_SESSION_KILL := 'ALTER SYSTEM KILL SESSION '''
                ||V_SID_SESSION||','||V_SERIAL_SESSION||'''';
            V_CURSOR_NAME_SESSION_KILL := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(V_CURSOR_NAME_SESSION_KILL, V_STMT_SESSION_KILL, DBMS_SQL.NATIVE);
            V_EXEC_SESSION_KILL := DBMS_SQL.EXECUTE(V_CURSOR_NAME_SESSION_KILL);
            DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION_KILL);
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION_KILL);
        DBMS_SQL.CLOSE_CURSOR(V_CURSOR_NAME_SESSION);
    END;
2. 실행방법 :
BEGIN
    KILL_ALL_USER_SESSION_DBMS_SQL('SCOTT');
    -- specify additional calls if necessary
END;
출처 : http://blogs.devart.com/dbforge/how-to-kill-user-sessions-in-oracle.html 3. 또다른 방법
select 'alter system kill session '''||sid||','||serial#||''';'
from v$session where module like 'abc21%';

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다