CREATE OR REPLACE PROCEDURE KILL_INACTIVE_SESSION
IS
V_SQLIMMEDIATE VARCHAR2 (100) := NULL;
BEGIN
FOR V_L_SESSION
IN (SELECT SID, SERIAL#, INST_ID
FROM GV$SESSION
WHERE STATUS = 'INACTIVE' AND LOGON_TIME < (SYSDATE - 3 / 24))
LOOP
V_SQLIMMEDIATE :=
'ALTER SYSTEM KILL SESSION '''
|| V_L_SESSION.SID
|| ','
|| V_L_SESSION.SERIAL#
|| ',@'
|| V_L_SESSION.INST_ID
|| ''' IMMEDIATE';
BEGIN
EXECUTE IMMEDIATE V_SQLIMMEDIATE;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--DBMS_OUTPUT.PUT_LINE (V_SQLIMMEDIATE);
END LOOP;
END;
/
2/ CREATE JOB RUN DAILY AT 12 AM
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SYS.JOB_KILL_INACTIVE_SESSION',
start_date => NULL,
repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
job_type => 'STORED_PROCEDURE',
job_action => 'SYS.KILL_INACTIVE_SESSION',
comments => NULL);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'RESTARTABLE',
VALUE => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'STOP_ON_WINDOW_CLOSE',
VALUE => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS
THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'JOB_PRIORITY',
VALUE => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'SYS.JOB_KILL_INACTIVE_SESSION',
attribute => 'AUTO_DROP',
VALUE => FALSE);
SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.JOB_KILL_INACTIVE_SESSION');
END;
/
