oracle查杀超过一小时的会话,自动杀掉堵塞别人超过2小时且自身是不活动的会话...

--先创建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;