Home > Oracle > Oracle DBMS_JOB – Session Killer

Oracle DBMS_JOB – Session Killer

Thursday, 11 November, 2010 Leave a comment Go to comments

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;
About these ads
Categories: Oracle Tags: ,
  1. Thursday, 11 November, 2010 at 12:31 pm

    select job, what from user_jobs;
    exec dbms_job.remove(2)
    exec dbms_job.run(10,true);
    SELECT job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION FROM user_jobs;

    next_date => TRUNC(SYSDATE+(1/24), ‘HH’)
    interval => ‘TRUNC(SYSDATE+(2/24/60),”MI”)’
    every 2 min

  2. Sunday, 21 September, 2014 at 3:11 pm

    I get pleasure from, cause I found exactly what I used to be taking a look for.
    You’ve ended my 4 day long hunt! God Bless you man. Have a nice day.
    Bye

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: