--先建立sqltext_kill_records和record_auto_kill_sessions表，後者使用create table record_auto_kill_sessions as select 'for迴圈中的語句，最後一個欄位加上sysdate'
--grant select on v_$session to dba_monitor;
--grant alter system to dba_monitor;
--再用job呼叫這個procedure就可以了

CREATE or REPLACE PROCEDURE proc_auto_kill_session AS
sqltext VARCHAR2(200) := '';
--ddl_cursor integer;
BEGIN
--check if existing any session which is blocking others
for cur_ses_locks in (select username,
sid,
serial#,
status,
LOGON_TIME,
seconds_in_wait,
LAST_CALL_ET,
sql_id,
PREV_SQL_ID,
event,
PROGRAM,
CLIENT_IDENTIFIER,
machine,
action,
PROCESS,
osuser
from v$session
where sid in (select FINAL_BLOCKING_SESSION
from v$session
where state = 'WAITING'
and BLOCKING_SESSION_STATUS = 'VALID'
and FINAL_BLOCKING_SESSION_STATUS = 'VALID')
and status = 'INACTIVE'
and sql_id is null
)
loop
--if the blocker is inactive form session which has blocked others > 600s then kill immediately
if cur_ses_locks.status = 'INACTIVE' and cur_ses_locks.event like 'SQL*Net message from client%' and cur_ses_locks.seconds_in_wait > 7200 then
sqltext := 'ALTER SYSTEM KILL SESSION ' || '''' || cur_ses_locks.sid || ',' || cur_ses_locks.serial# || '''';
--ddl_cursor := dbms_sql.open_cursor;
--dbms_sql.parse(ddl_cursor, sqltext, dbms_sql.native);
EXECUTE IMMEDIATE sqltext;
insert into dba_monitor.record_auto_kill_sessions
values
(cur_ses_locks.username
cur_ses_locks.sid,
cur_ses_locks.serial#,
cur_ses_locks.status,
cur_ses_locks.LOGON_TIME,
cur_ses_locks.seconds_in_wait,
cur_ses_locks.LAST_CALL_ET,
cur_ses_locks.sql_id,
cur_ses_locks.PREV_SQL_ID,
cur_ses_locks.event,
cur_ses_locks.PROGRAM,
cur_ses_locks.CLIENT_IDENTIFIER,
cur_ses_locks.machine,
cur_ses_locks.action
cur_ses_locks.PROCESS
cur_ses_locks.osuser
sysdate);
insert into dba_monitor.sqltext_kill_records (script) values (sqltext);
commit;
end if;
end loop;
END;

下面這個procedure也不錯
CREATE or REPLACE PROCEDURE proc_auto_kill_session AS
sqltext VARCHAR2(200) := '';
--ddl_cursor integer;
BEGIN
--check if existing any session which is blocking others
for cur_ses_locks in (select r.root_sid,
s.serial#,
s.client_identifier,
r.blocked_num,
r.avg_wait_seconds,
s.username,
s.status,
s.event,
s.machine,
s.program,
s.sql_id,
s.prev_sql_id
from (select root_sid,
avg(seconds_in_wait) as avg_wait_seconds,
count(*) - 1 as blocked_num
from (select CONNECT_BY_ROOT sid as root_sid,
seconds_in_wait
from v$session
start with blocking_session is null
connect by prior sid = blocking_session)
group by root_sid
having count(*) > 1) r,
v$session s
where r.root_sid = s.sid
)
loop
--if the blocker is inactive form session which has blocked others > 600s then kill immediately
if cur_ses_locks.status = 'INACTIVE' and cur_ses_locks.event like 'SQL*Net message from client%' and cur_ses_locks.avg_wait_seconds > 1800 then
sqltext := 'ALTER SYSTEM KILL SESSION ' || '''' || cur_ses_locks.root_sid || ',' || cur_ses_locks.serial# || '''';
--ddl_cursor := dbms_sql.open_cursor;
--dbms_sql.parse(ddl_cursor, sqltext, dbms_sql.native);
EXECUTE IMMEDIATE sqltext;
insert into dba_monitor.record_auto_kill_sessions
values
(cur_ses_locks.root_sid,
cur_ses_locks.serial#,
cur_ses_locks.client_identifier,
cur_ses_locks.blocked_num,
cur_ses_locks.avg_wait_seconds,
cur_ses_locks.username,
cur_ses_locks.status,
cur_ses_locks.event,
cur_ses_locks.machine,
cur_ses_locks.program,
cur_ses_locks.sql_id,
cur_ses_locks.prev_sql_id,
sysdate);
insert into dba_monitor.sqltext_kill_records (script) values (sqltext);
commit;
end if;
end loop;
END;

自動殺掉堵塞別人超過2小時且自身是不活動的會話

相關文章