Oracle DBMS_JOB – Session Killer

Note This package is deprecated and has been supplanted by DBMS_SCHEDULER.
Source {ORACLE_HOME}/rdbms/admin/dbmsjob.sql
Dependencies job$, dba_jobs, all_jobs, user_jobs, dba_jobs_running, all_jobs_running, user_jobs_running

Job Intervals
Execute daily 'SYSDATE + 1'
Execute once per week 'SYSDATE + 7'
Execute hourly 'SYSDATE + 1/24'
Execute every 10 min. 'SYSDATE + 10/1440'
Execute every 30 sec. 'SYSDATE + 30/86400'
Do not re-execute NULL


Security Model Execute is granted to PUBLIC with GRANT option

Stored Procedure

create or replace procedure kill_session(p_program in varchar)
as
begin
    for x in (select *
                 from v$session 
                where lower(PROGRAM) = lower(p_program) and upper(USERNAME) <> 'ADMIN')
    loop
        execute immediate 'alter system kill session ''' || 
                 x.sid || ',' || x.serial# || '''';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;
/

JOB

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what     => 'kill_session(''cpi2.exe'');'
     ,next_date => to_date('11.11.2010 03:50:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE+1)'
     ,no_parse  => false
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;