DBMS_JOB not running, urgent please help - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: DBMS_JOB not running, urgent please help

  1. #11
    Join Date
    Apr 2002
    Posts
    291

    To jurij & Julian

    Jurij/Julian,
    I tried with your code, and here i'm pasting the same, please look at these:

    SQL> variable jobno number
    SQL> begin
    2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
    3 sysdate,
    4 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
    5 end;
    6 /

    PL/SQL procedure successfully completed.

    SQL> print jobno

    JOBNO
    ----------
    172

    SQL> select JOB,SCHEMA_USER,LAST_DATE,NEXT_DATE,NEXT_SEC from user_jobs;

    JOB SCHEMA_USER LAST_DATE NEXT_DATE NEXT_SEC
    ---------- ------------------------------ --------- --------- --------
    169 CMS2ADM 09-JUL-02 15:28:54
    170 CMS2ADM 09-JUL-02 16:37:53
    90 CMS2ADM 08-JUL-02 09-JUL-02 10:35:25
    172 CMS2ADM 09-JUL-02 03:59:06

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    09-JUL-02

    SQL> select NEXT_DATE,NEXT_SEC,THIS_DATE,THIS_SEC,INTERVAL from user_jobs
    2 where job=172;

    NEXT_DATE NEXT_SEC THIS_DATE THIS_SEC
    --------- -------- --------- --------
    INTERVAL
    --------------------------------------------------------------------------------
    09-JUL-02 03:59:06
    NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24



    Here, i'm confused, NEXT_DATE,NEXT_SEC is showing as the date and time at which i created the job. Can you please tell me how to check it up when my job is going to fire again ?? as per we set in the code???








    PNRDBA

  2. #12
    Join Date
    Apr 2002
    Posts
    291
    Jurij/Julian ,
    Here again i'm pasting some more results after i set the job as you mentioned. Here i'm confused like, next_date is showing as 09-JUL-02, which is today. But interval is showing as correct . Can you please clarify??

    SQL> select INTERVAL from user_jobs where job=172;

    INTERVAL
    --------------------------------------------------------------------------------
    NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24

    SQL> select NEXT_DATE,NEXT_SEC from user_jobs where job=172;

    NEXT_DATE NEXT_SEC
    --------- --------
    09-JUL-02 03:59:06
    PNRDBA

  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Your job 172 has not been run jet, it is still waiting to be run for the first time. You can see this by looking at LAST_DATE column, it is NULL. So the actual NEXT_DAT this job needs to be run is actually allready in the past, ie the moment you submited it. And why it has not been run yet? Because you didn't COMMIT your job submition. Until you enter COMMIT, you can see this job in DBA_JOBS only from the session that submitted it, while the RDBMS system does not see it yet, so it can't run it.

    As soon as you commit your submition, the job will run and the NEXT_DATE will be update correctly to next SUNDAY.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #14
    Join Date
    Apr 2002
    Posts
    291
    I'm sorry jurij, forgot to put a commit there. Here is the code i used with commit , but still getting the same out put.

    SQL> variable jobno number
    SQL> begin
    2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',sysdate,
    3 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
    4 commit;
    5 end;
    6 /

    PL/SQL procedure successfully completed.

    SQL> print jobno

    JOBNO
    ----------
    41

    SQL> select THIS_DATE,THIS_SEC,NEXT_DATE, NEXT_SEC,INTERVAL from user_jobs where job=41;

    THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC
    --------- -------- --------- --------
    INTERVAL
    --------------------------------------------------------------------------------
    09-JUL-02 05:01:51 09-JUL-02 05:01:47
    NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24


    Here it's still showing next_date as today's date only. Did i do any mistake again?? Please help me
    PNRDBA

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Your job is running just now! THIS_DATE and THIS_SEC columns are not null only during the execution of the job. And during that time, NEXT_DATE and NEXT_SEC are set to the same values as THIS_DATE and THIS_SEC.

    As soon as the job completes, the values in THIS_DATE/THIS_SEC will disappear and the NEXT_DATE/NEXT_SEC will be set to the next sunday.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    Apr 2002
    Posts
    291
    Jurij,
    Please help me!!!
    PNRDBA

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I bet your job is repeating itself because there are unhandeled exceptions in your procedure. What does the following query return:

    SELECT failures, total_time, last_sec, this_sec, next_sec last_date, next_date FROM user_jobs
    WHERE job_id = 41; -- or whatever your job number is
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Sep 2000
    Posts
    305
    See there are two parameter in the job and you are passing sysdate and the 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');

    so in case of first parameter which is sysdate it will fire as soon as you run that job and the second parameter is the due date parameter so do one thing put this string(NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24) in both the parameter

    begin
    DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
    NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24,
    'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
    end;

    Hope this will work

    Shailendra

  9. #19
    Join Date
    Apr 2002
    Posts
    291
    Jurij,
    This is the output. I got 1 failure. What can be derived from this???


    1 SELECT failures, total_time, last_sec, this_sec, next_sec last_date, next_date FROM user_jobs
    2* WHERE job = 44
    3 /

    FAILURES TOTAL_TIME LAST_SEC THIS_SEC LAST_DAT NEXT_DATE
    --------- ---------- -------- -------- -------- ---------
    1 247 07:25:33 07:30:15 07:30:14 09-JUL-02


    PNRDBA

  10. #20
    Join Date
    Apr 2002
    Posts
    291
    SQL> select job from dba_jobs_running;

    no rows selected

    SQL> select THIS_DATE,THIS_SEC,NEXT_DATE, NEXT_SEC,INTERVAL from user_jobs where job=44;

    THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC
    --------- -------- --------- --------
    INTERVAL
    --------------------------------------------------------------------------------
    09-JUL-02 07:47:54
    NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24

    Jurij,
    now my job is not running it seems, atleast now, it has to show me the next sunday's date. but here it's not. And i gone throught the complete procedure there is no exceptions which are unhandled. What might be the problem??
    PNRDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width