CODEWITHSUNDEEP
oracle-database
Muhammad Faisal
Muhammad Faisal

Reputation: 135

Oracle job scheduler not working

i create a job in oracle which will run in every minute but the problem is job is create successfully but not run on time 

execute PACKAGE_BATCH.USP_TERMIANTE_SUSPENSION;
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"LTR"."TERMINATE_SUSPENSION_JOB"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'LTR.PACKAGE_BATCH.USP_TERMINATE_SUSPENSION',
            number_of_arguments => 0,
            start_date => sysdate,
            repeat_interval => 'FREQ=MINUTELY;BYMINUTE=1',
            end_date => NULL,
            enabled => TRUE,
            auto_drop => TRUE,
            comments => 'Terminate Suspension When End Date is equal to Current Date');

            DBMS_SCHEDULER.set_attribute
            ( name => '"LTR"."TERMINATE_SUSPENSION_JOB"',
            attribute => 'job_action', 
            value => 'LTR.PACKAGE_BATCH.USP_TERMINATE_SUSPENSION');

            DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"LTR"."TERMINATE_SUSPENSION_JOB"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);

    DBMS_SCHEDULER.enable(
             name => '"LTR"."TERMINATE_SUSPENSION_JOB"');
END;

Upvotes: 2

Views: 1063

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

To keep it simple, see these examples:

  • Every minute

Repeat interval using calendaring syntax.

'freq=minutely;'

Repeat interval using dates and timestamps.

'sysdate + 1/24/60'
'systimestamp + 1/24/60'

'sysdate + interval ''1'' minute'
'systimestamp + interval ''1'' minute'
  • Every minute, on the minute

Repeat interval using calendaring syntax.

'freq=minutely; bysecond=0;'

Repeat interval using dates and timestamps.

'trunc(sysdate, ''MI'') + 1/24/60'
'trunc(systimestamp, ''MI'') + 1/24/60'

'trunc(sysdate, ''MI'') + interval ''1'' minute'
'trunc(systimestamp, ''MI'') + interval ''1'' minute'

More example here http://oracle-base.com/articles/10g/scheduler-10g.php#every_minute

Upvotes: 1

ruudvan
ruudvan

Reputation: 1371

FREQ=MINUTELY;BYMINUTE=1

This will make the job run at every "1"st minute. So 12:01, 1:01, 2:01 etc.

If you need to make it run every minute - FREQ=MINUTELY is sufficient or you can add BYSECOND=0 if you want to ensure it runs at exactly 12:01:00, 12:02:00, 12:03:00 etc.

Upvotes: 4

Related Questions