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

1. 프로시저









1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


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. 실행방법 :







1
2
3
4


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. 또다른 방법







1
2


select 'alter system kill session '''||sid||','||serial#||''';'
from v$session where module like 'abc21%';