5

I want to create oracle scheduler job which runs daily at 20:00 and runs for 30 minute. This job will delete the rows from KPI_LOGS table as this table contains large amount of data and it continues to grow. I have created the below script in oracle sql developer for such job but not sure if this is correct or not as i am new to scheduler job concept.

    BEGIN
        DBMS_SCHEDULER.CREATE_JOB (
                job_name => '"RATOR_MONITORING"."CROP_KPI_LOGS"',
                job_type => 'PLSQL_BLOCK',
                job_action => 'DELETE FROM KPI_LOGS WHERE CAST(TIMESTAMP AS DATE) < (SYSDATE  - 28);',
                number_of_arguments => 0,
                start_date => NULL,
                repeat_interval => 'FREQ=DAILY;INTERVAL=30',
                end_date => NULL,
                enabled => FALSE,
                auto_drop => FALSE,
                comments => 'CROP_KPI_LOGS');    

        DBMS_SCHEDULER.SET_ATTRIBUTE( 
                 name => '"RATOR_MONITORING"."CROP_KPI_LOGS"', 
                 attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);



    DBMS_SCHEDULER.enable(
             name => '"RATOR_MONITORING"."CROP_KPI_LOGS"');
END;
Allan
  • 17,141
  • 4
  • 52
  • 69
Andrew
  • 3,632
  • 24
  • 64
  • 113
  • 1
    What do you mean by "runs for 30 minutes"? Normally, the job would run for as long as it needed to run in order for your `delete` statement to run. What do you want to happen if the `delete` statement takes more than 30 minutes? Do you want the job to be killed and the work the statement had done thus far rolled back? Do you want the job to delete as much as it can in 30 minutes and then stop at the end of the window? Something else? – Justin Cave Jul 28 '15 at 15:10

1 Answers1

8

the repeat_internal is incorrect, what you have there will run every 30 days. to run at 8pm each day go for...

FREQ=DAILY; BYHOUR=20

you can't dictate how long it will run for, it will take as long as your DELETE statement takes

davegreen100
  • 2,055
  • 3
  • 13
  • 24
  • 1
    Thanks it works for me. Just in oracle sql developer it asks for BYDAY also so i have given BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN also and it works :) – Andrew Jul 29 '15 at 07:48