Example of DBMS_SCHEDULER

alter session set time_zone = 'Asia/Jerusalem';


declare

start_date TIMESTAMP WITH TIME ZONE;

begin

start_date :=  TO_TIMESTAMP_TZ('2006/05/11 16:50:00.000000 '||TZ_OFFSET('Asia/Jerusalem'),'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm');

--start_date :=  TO_TIMESTAMP_TZ('2006/05/11 16:40:00','yyyy/mm/dd hh24:mi:ss');

--start_date := systimestamp at time zone '+02:00'

DBMS_SCHEDULER.CREATE_JOB(

     job_name            =>'alex_test_10',

     job_type          => 'STORED_PROCEDURE',

     job_action          => 'alex_text',

     start_date          =>  start_date,    

     enabled => true );

end;

/

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ( 'default_timezone', 'Asia/Jerusalem' );

exec DBMS_SCHEDULER.ENABLE( name => 'alex_test1' );

declare

begin

DBMS_SCHEDULER.CREATE_JOB(

     job_name            =>'alex_test78',

     job_type          => 'STORED_PROCEDURE',

     job_action          => 'alex_text',   

     start_date     => SYSTIMESTAMP + INTERVAL '10' MINUTE,

     enabled => true );

end;

/

declare

plsql_block VARCHAR2(4000);

begin

plsql_block := 'DECLARE

                BEGIN

                   EXECUTE IMMEDIATE ''CREATE TABLE ALEX_TTTTTT ( id number )'';

                END;';             

DBMS_SCHEDULER.CREATE_JOB(

     job_name            =>'alex_test',

     job_type          => 'PLSQL_BLOCK',

     job_action          => plsql_block,   

     start_date     => SYSTIMESTAMP + INTERVAL '10' MINUTE,

     enabled => true );

end;

/

In the case the stored procedure should get one or more parameters, do the following:

DECLARE

  start_date TIMESTAMP;

BEGIN

start_date :=  TO_TIMESTAMP('2008/08/01 00:10:00','yyyy/mm/dd hh24:mi:ss');

DBMS_SCHEDULER.CREATE_JOB(

      job_name            => 'REF_HT_CURR_YEAR_HETERS_SNP',

      job_type            => 'STORED_PROCEDURE',

      job_action          => 'HT_NEW.REFRESH_HETER_SNP',

      start_date          => start_date,

      repeat_interval     => 'FREQ=DAILY; INTERVAL=1',

      number_of_arguments => 1

      );

 END;

/

BEGIN

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

   job_name                => 'REF_HT_CURR_YEAR_HETERS_SNP',

   argument_position       => 1,

   argument_value          => 'HT_CURRENT_YEAR_HETERS_SNP');

END;  

/

EXEC DBMS_SCHEDULER.ENABLE ( 'REF_HT_CURR_YEAR_HETERS_SNP' );



-- CHANGE JOB ATTRIBUTES
begin
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'REF_HT_CURR_YEAR_HETERS_SNP',
                 attribute => 'repeat_interval',
                 value => 'FREQ=MINUTELY; INTERVAL=30'
    );
end;



begin
DBMS_SCHEDULER.SET_ATTRIBUTE(   name => 'REF_HT_CURR_YEAR_HETERS_SNP',
                 attribute => 'repeat_interval',
                 value => 'FREQ=DAILY; BYDAY=MON; BYHOUR=07'
    );
end;


begin
DBMS_SCHEDULER.SET_ATTRIBUTE(    name => 'TEST_JOB',
                 attribute => 'job_action',
                 value => '/u01/app/oracle/scripts/test_job.ksh'
    );
end;



=========================================================


Run job in background (not from the current session):



BEGIN
  -- Run job synchronously.
  DBMS_SCHEDULER.run_job (job_name => 'AUTO_COMPRESS_DAILY',
                          use_current_session => FALSE);
END;



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



begin
-- daily from Monday to Sunday at 22:00 (10:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_DAILY_2200',
 start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
 repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
 comments=>'Runtime: Every day (Mon-Sun) at 22:00 o'clock');

-- run every hour, every day
dbms_scheduler.create_schedule(  
schedule_name  => 'INTERVAL_EVERY_HOUR',  
  start_date    => trunc(sysdate)+18/24,  
  repeat_interval => 'freq=HOURLY;interval=1',  
  comments     => 'Runtime: Every day every hour');  

-- run every 5 minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_5_MINUTES',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=5',
  comments     => 'Runtime: Every day all 5 minutes');

-- run every minute, every day
dbms_scheduler.create_schedule(
schedule_name  => 'INTERVAL_EVERY_MINUTE',
  start_date    => trunc(sysdate)+18/24,
  repeat_interval => 'freq=MINUTELY;interval=1',
  comments     => 'Runtime: Every day every minute');

-- run every Sunday at 18:00 (06:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_EVERY_SUN_1800',
 start_date=> trunc(sysdate)+18/24,
 repeat_interval=> 'FREQ=DAILY; BYDAY=SUN; BYHOUR=18;',
 comments=>'Runtime: Run at 6pm every Sunday');
end;