Вот, накидал по быстрому вариант. Я против такого извращения, но, один из клиентов попросил.
SQL код:
CREATE OR REPLACE PROCEDURE disconnect_idle_sessions
AS
BEGIN
FOR s IN (SELECT s.sid, s.serial# FROM v$session s, supermag.sslocks l WHERE status='INACTIVE' AND prev_exec_start < SYSDATE - INTERVAL '20' MINUTE AND program = 'Sm.Main.exe' and S.SERIAL# = L.SERNUM)
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM DISCONNECT SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
EXECUTE IMMEDIATE 'DELETE FROM SUPERMAG.SSLOCKS where sid=' || s.sid || ' and sernum=' || s.serial#;
COMMIT;
exception when others then null;
END;
END LOOP;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'disconnect_idle_sessions_job',
job_type => 'STORED_PROCEDURE',
job_action => 'disconnect_idle_sessions',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
enabled => TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'disconnect_idle_sessions_job',
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_OFF);
END;
/