Oracle Job Scheduler doesn't repeat the job_action

Question

I want to repeate a Job every 5 minutes. I have a test table that I fill with random dates. If there are dates older then SYSDATE-5, than I want to delete them. The following code works only the first time I start the scheduler and it never repeats the job_action agaian:

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB (
        job_name => '"AUTHMGR"."Test2"',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN DELETE FROM TEST WHERE TESTDATE < SYSDATE-5;END;',
        number_of_arguments => 0,
        start_date => SYSDATE,
        repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
        end_date => NULL,
        job_class => '"SYS"."DEFAULT_JOB_CLASS"',
        enabled => TRUE,
        auto_drop => FALSE,
        comments => 'Test');
END; 
/

Do I use the repeat_interval with wrong FREQ and wrong INTERVAL?

I use the Scheduler in Oracle SQL Developer.

HOw are you determining that the job did not run again - by looking in your TEST table, or querying the job logs? — OldProgrammer
You might want to commit also. Whats the contents of dba_scheduler_jobs for this one? — tbone
I was looking in my TEST table an didn't see any changes. I closed the Developer and opened again and saw the repeater did occur. Do I need to extend my code with COMMIT statement? Where do i put COMMIT? After END — Georg

Georg Georg · Accepted Answer · 2014-01-02T10:10:33

0

votes

The problem was with the INSERT statements. There was no COMMIT after INSERT.

Oracle Job Scheduler doesn't repeat the job_action

1 Answers