組織分工下,通常不會把kill session的權限給AP 單位,但有時會緊急接到要砍資料庫連線的需求,不過DBA又不是英英美代只,隨時待命準備幫你砍session,只好寫了一隻store procedure 給AP單位來自行砍session,但這只是應急,奉勸還是要找到根本原因,想辦法去解決問題,那才是做事情的基本道理
create or replace procedure kill_session(sid_in varchar2, serial_num_in varchar2) authid definer
as
not_allowed exception;
killed exception;
ses_rec v$session%rowtype;
v_file UTL_FILE.FILE_TYPE;
kill_log_file varchar2(100) := '.kill_ses_'||to_char(sysdate,'YYYYMMDD')||'.log';
current_session_user varchar2(100);
client_host varchar2(100);
client_ip varchar2(100);
client_terminal varchar2(100);
client_module varchar2(100);
msg varchar2(100);
begin
execute immediate('create or replace directory kill_sess_log_dir as ''/tmp''');
select * into ses_rec
from v$session
where sid = sid_in
and serial# = serial_num_in;
if ses_rec.username in ( 'SYS','SYSTEM','DBSNMP','SYSMAN') or ses_rec.username is null
then
raise not_allowed;
end if;
if ses_rec.status = 'KILLED'
then
raise killed;
end if;
execute immediate 'alter system kill session ''' || sid_in || ', ' || serial_num_in || '''' ;
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV','HOST'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','TERMINAL'),
SYS_CONTEXT('USERENV','MODULE')
into current_session_user ,
client_host,
client_ip ,
client_terminal ,
client_module
FROM DUAL;
v_file := UTL_FILE.FOPEN(location => 'KILL_SESS_LOG_DIR',
filename => kill_log_file,
open_mode => 'a',
max_linesize => 32767);
msg := to_char(sysdate,'YYYYMMDDHH24MISS')||','||current_session_user||','||client_host||','||client_ip||','||client_terminal ||','||sid_in||','||serial_num_in||','||client_module ;
utl_file.put_line(v_file,msg);
utl_file.fclose(v_file);
exception
when no_data_found
then
dbms_output.put_line('Your input find no session');
when not_allowed
then
dbms_output.put_line('Hey! You are not allowed to kill this session.');
when killed
then
dbms_output.put_line('This session has already been killed.');
when others
then
declare
error_code number := SQLCODE;
error_msg varchar2(300) := SQLERRM;
begin
if error_code != 0 then
dbms_output.put_line(error_msg);
if UTL_FILE.IS_OPEN(v_file) then
utl_file.fclose(v_file);
end if;
end if;
end;
end;
/
create or replace procedure kill_session(sid_in varchar2, serial_num_in varchar2) authid definer
as
not_allowed exception;
killed exception;
ses_rec v$session%rowtype;
v_file UTL_FILE.FILE_TYPE;
kill_log_file varchar2(100) := '.kill_ses_'||to_char(sysdate,'YYYYMMDD')||'.log';
current_session_user varchar2(100);
client_host varchar2(100);
client_ip varchar2(100);
client_terminal varchar2(100);
client_module varchar2(100);
msg varchar2(100);
begin
execute immediate('create or replace directory kill_sess_log_dir as ''/tmp''');
select * into ses_rec
from v$session
where sid = sid_in
and serial# = serial_num_in;
if ses_rec.username in ( 'SYS','SYSTEM','DBSNMP','SYSMAN') or ses_rec.username is null
then
raise not_allowed;
end if;
if ses_rec.status = 'KILLED'
then
raise killed;
end if;
execute immediate 'alter system kill session ''' || sid_in || ', ' || serial_num_in || '''' ;
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER'),
SYS_CONTEXT('USERENV','HOST'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','TERMINAL'),
SYS_CONTEXT('USERENV','MODULE')
into current_session_user ,
client_host,
client_ip ,
client_terminal ,
client_module
FROM DUAL;
v_file := UTL_FILE.FOPEN(location => 'KILL_SESS_LOG_DIR',
filename => kill_log_file,
open_mode => 'a',
max_linesize => 32767);
msg := to_char(sysdate,'YYYYMMDDHH24MISS')||','||current_session_user||','||client_host||','||client_ip||','||client_terminal ||','||sid_in||','||serial_num_in||','||client_module ;
utl_file.put_line(v_file,msg);
utl_file.fclose(v_file);
exception
when no_data_found
then
dbms_output.put_line('Your input find no session');
when not_allowed
then
dbms_output.put_line('Hey! You are not allowed to kill this session.');
when killed
then
dbms_output.put_line('This session has already been killed.');
when others
then
declare
error_code number := SQLCODE;
error_msg varchar2(300) := SQLERRM;
begin
if error_code != 0 then
dbms_output.put_line(error_msg);
if UTL_FILE.IS_OPEN(v_file) then
utl_file.fclose(v_file);
end if;
end if;
end;
end;
/