Posts Tagged ‘job’
Oracle DBMS_JOB – Session Killer
Thursday, 11 November, 2010
1 comment
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;
Recent Comments