Forum Stats

  • 3,826,393 Users
  • 2,260,641 Discussions
  • 7,896,930 Comments

Discussions

COMPLICATIONS WITH JOB SCHEDULING

Bala_Dev
Bala_Dev Member Posts: 43
edited Nov 14, 2013 6:49AM in General Database Discussions

This was the table which i created...

CREATE TABLE dummy(Serial NUMBER(6),Moment VARCHAR2(100));

The below is the procedure that i used fro my job on the above table...

CREATE OR REPLACE PROCEDURE dummy_dummy IS

a NUMBER(6):=0;

BEGIN

FOR i IN A..10 LOOP

INSERT INTO dummy VALUES(a,TO_CHAR(SYSDATE,'DD:MON:YYYY:HH24:MI:SS'));

a:=a+1;

END LOOP;

END dummy_dummy;

And, the below is my job coding....

BEGIN

DBMS_SCHEDULER.CREATE_JOB

(

job_name                  =>  'for_dummy_dummy_proc',

job_type                  =>  'PLSQL_BLOCK',

job_action                =>  'BEGIN dummy_dummy; END;',

start_date                =>  SYSTIMESTAMP,

repeat_interval           =>  'freq=minutely; interval=1;',

end_date                  =>  NULL,

enabled                   =>  TRUE,

comments                  =>  'This JOB is to update dummy values in the DUMMY table'

);

END;

BEGIN

DBMS_SCHEDULER.RUN_JOB

(

job_name              =>  'for_dummy_dummy_proc'

);

END;

Everything went on fine except the first run of the scheduling....

At 49 minute... it ran two times at 23 rd and 28 th.

But, then after it ran fine once a minute on every 23 rd second. Why am I facing this problem????

The output is as below.

814:NOV:2013:15:55:23
914:NOV:2013:15:55:23
014:NOV:2013:15:54:23
114:NOV:2013:15:54:23
214:NOV:2013:15:54:23
314:NOV:2013:15:54:23
414:NOV:2013:15:54:23
1014:NOV:2013:15:54:23
814:NOV:2013:15:54:23
714:NOV:2013:15:54:23
614:NOV:2013:15:54:23
514:NOV:2013:15:54:23
914:NOV:2013:15:54:23
014:NOV:2013:15:53:23
114:NOV:2013:15:53:23
214:NOV:2013:15:53:23
314:NOV:2013:15:53:23
414:NOV:2013:15:53:23
514:NOV:2013:15:53:23
614:NOV:2013:15:53:23
714:NOV:2013:15:53:23
814:NOV:2013:15:53:23
914:NOV:2013:15:53:23
1014:NOV:2013:15:53:23
414:NOV:2013:15:52:23
314:NOV:2013:15:52:23
214:NOV:2013:15:52:23
114:NOV:2013:15:52:23
014:NOV:2013:15:52:23
514:NOV:2013:15:52:23
1014:NOV:2013:15:52:23
914:NOV:2013:15:52:23
814:NOV:2013:15:52:23
714:NOV:2013:15:52:23
614:NOV:2013:15:52:23
014:NOV:2013:15:51:23
114:NOV:2013:15:51:23
214:NOV:2013:15:51:23
314:NOV:2013:15:51:23
414:NOV:2013:15:51:23
1014:NOV:2013:15:51:23
614:NOV:2013:15:51:23
714:NOV:2013:15:51:23
814:NOV:2013:15:51:23
914:NOV:2013:15:51:23
514:NOV:2013:15:51:23
414:NOV:2013:15:50:23
314:NOV:2013:15:50:23
214:NOV:2013:15:50:23
114:NOV:2013:15:50:23
014:NOV:2013:15:50:23
514:NOV:2013:15:50:23
614:NOV:2013:15:50:23
714:NOV:2013:15:50:23
814:NOV:2013:15:50:23
914:NOV:2013:15:50:23
1014:NOV:2013:15:50:23
014:NOV:2013:15:49:28
114:NOV:2013:15:49:28
1014:NOV:2013:15:49:28
914:NOV:2013:15:49:28
814:NOV:2013:15:49:28
714:NOV:2013:15:49:28
614:NOV:2013:15:49:28
514:NOV:2013:15:49:28
414:NOV:2013:15:49:28
314:NOV:2013:15:49:28
214:NOV:2013:15:49:28
014:NOV:2013:15:49:23
914:NOV:2013:15:49:23
814:NOV:2013:15:49:23
714:NOV:2013:15:49:23
614:NOV:2013:15:49:23
1014:NOV:2013:15:49:23
414:NOV:2013:15:49:23
314:NOV:2013:15:49:23
214:NOV:2013:15:49:23
114:NOV:2013:15:49:23
514:NOV:2013:15:49:23
Tagged:
Bala_Dev

Best Answer

  • Bala_Dev
    Bala_Dev Member Posts: 43
    Answer ✓

    JohnWatson

    Thank You very much.

    It should be like

    1.I should either ENABLE it in the DBMS_SCHEDULER.CREATE_JOB

    or

    2.Keep it DISABLED at the creation time and LATER RUN it using DBMS_SCHEDULER.RUN_JOB.

    I did the first thing alone.

    Realized my mistake.

    Thank you once again.

«1

Answers

  • VMahadev
    VMahadev Member Posts: 54

    What are the values for this_date and next_date for this job in dba_jobs table?

    Bala_Dev
  • VMahadev
    VMahadev Member Posts: 54

    Please list values for all the columns for this job from dba_jobs?

  • Bala_Dev
    Bala_Dev Member Posts: 43

    I queried it.... But, there were no entry in the table. But, there is an entry in dba_scheduler_jobs instead.

  • Bala_Dev
    Bala_Dev Member Posts: 43

    14-NOV-13 04.37.23.010000000 PM +05:30

    14-NOV-13 04.38.23.000000000 PM +05:30

    THE above are the last start_date and next_start_date in dba_scheduler_jobs for that table.

  • JohnWatson
    JohnWatson Member Posts: 2,461 Gold Trophy
    Balamurali.P.C wrote:
    
    This was the table which i created...
    CREATE TABLE dummy(Serial NUMBER(6),Moment VARCHAR2(100));
    
    The below is the procedure that i used fro my job on the above table...
    CREATE OR REPLACE PROCEDURE dummy_dummy IS
    a NUMBER(6):=0;
    BEGIN
    FOR i IN A..10 LOOP
    INSERT INTO dummy VALUES(a,TO_CHAR(SYSDATE,'DD:MON:YYYY:HH24:MI:SS'));
    a:=a+1;
    END LOOP;
    END dummy_dummy;
    
    And, the below is my job coding....
    BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name                  =>  'for_dummy_dummy_proc',
    job_type                  =>  'PLSQL_BLOCK',
    job_action                =>  'BEGIN dummy_dummy; END;',
    start_date                =>  SYSTIMESTAMP,
    repeat_interval           =>  'freq=minutely; interval=1;',
    end_date                  =>  NULL,
    enabled                   =>  TRUE,
    comments                  =>  'This JOB is to update dummy values in the DUMMY table'
    );
    END;
    
    
    BEGIN
    DBMS_SCHEDULER.RUN_JOB
    (
    job_name              =>  'for_dummy_dummy_proc'
    );
    END;
    
    Everything went on fine except the first run of the scheduling....
    At 49 minute... it ran two times at 23 rd and 28 th.
    But, then after it ran fine once a minute on every 23 rd second. Why am I facing this problem????
    
    
    
    
    
    
    
    
    

    It isn't problem, you have the expected behaviour: you scheduled it to run every minute, starting from 49:23 when you created the job as ENABLED, and then you ran it manually at 49:28.

    Incidentally, the line a:=a+1  is not doing anything for you.

    Bala_DevJohnWatson
  • Bala_Dev
    Bala_Dev Member Posts: 43
    edited Nov 14, 2013 6:26AM

    JohnWatson

    Thank you very much. Do you mean to say that it runs once the job is CREATED AND ALSO PUTS THE ENTRY INTO THE TABLE???

    Is it like simply if i create a job, the table will get updated JUST FOR CREATE???

  • VMahadev
    VMahadev Member Posts: 54

    Yes..Unless you break it

    Bala_Dev
  • Bala_Dev
    Bala_Dev Member Posts: 43
    edited Nov 14, 2013 6:25AM

    1db95704-3e2e-4907-9e49-b5c3f98b9612

    You mean the DROP_JOB thing???

    Sure about that???

  • JohnWatson
    JohnWatson Member Posts: 2,461 Gold Trophy
    edited Nov 14, 2013 6:27AM
    1db95704-3e2e-4907-9e49-b5c3f98b9612 wrote:
    
    Yes..Unless you break it
    

    This is your second reply showing that you re not aware of the difference between dbms_job and dbms_scheduler.

    Bala_Dev
  • Bala_Dev
    Bala_Dev Member Posts: 43
This discussion has been closed.