NEXT_DATE in dba_jobs is stuck
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: NEXT_DATE in dba_jobs is stuck

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    NEXT_DATE in dba_jobs is stuck

    Hi all,

    I was faced with something really strange:
    The jobs are not run,as i can see in dba_jobs.
    Also,there are no rows selected in dba_running_jobs.
    As you can see from the query from dba_jobs,all the NEXT_DATE values arec stuck on 29/1/04.
    All of them are not broken and their FAILURES column shows 0 failures.
    I've checked the init*.ora parameters:
    SQL> show parameter job

    What could be the problem?

    Thanks in advance,
    Nir

    NAME TYPE VALUE
    ------------------------------------ ------- ------------------------------
    job_queue_interval integer 60
    job_queue_processes integer 6


    SQL> select job,LOG_USER "log_usr",PRIV_USER "priv_usr",SCHEMA_USER "schema",LAST_DATE,
    2 NEXT_DATE,INTERVAL,FAILURES "fails" ,broken,what
    3 from dba_jobs;

    JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
    ----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --
    1 TRP TRP TRP 30/01/04 00:00:00 to_date( to_cha N TRP.REACTIVATE_FILTERS;
    r(sysdate+2,'DD
    /MM/YYYY'),'DD/
    MM/YYYY HH24:MI
    :SS')

    2 TRP TRP TRP 29/01/04 00:01:15 sysdate + 1 N TRP.DELETE_OLD_LOGGING;
    3 TRP TRP TRP 29/01/04 00:01:15 sysdate + 1/24 N TRP.CASE_SEVERITY_CALCULA
    4 TRP TRP TRP 29/01/04 00:01:15 sysdate + 1/24 N TRP.FIX_ALERTS_SUMMARY;
    5 TRP TRP TRP 29/01/04 00:01:51 SYSDATE + (15/1 N Cdrsummary('TBL_CDR_SUM_1
    440)

    JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
    ----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --

    6 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
    7 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
    8 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
    9 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
    10 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
    11 TRP TRP TRP 29/01/04 00:15:19 SYSDATE+1/24/12 N CREATE_SUM_TABLES.CREATE_
    12 TRP TRP TRP 29/01/04 00:02:14 sysdate+1/288 N dbms_refresh.refresh('"TR
    13 TRP TRP TRP 29/01/04 00:02:15 sysdate+1/288 N dbms_refresh.refresh('"TR
    14 TRP TRP TRP 29/01/04 00:02:15 sysdate+1/288 N dbms_refresh.refresh('"TR
    15 TRP TRP TRP 29/01/04 00:02:15 sysdate+1/288 N dbms_refresh.refresh('"TR

    JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
    ----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --
    16 TRP TRP TRP 29/01/04 00:02:16 sysdate+1/288 N dbms_refresh.refresh('"TR
    17 TRP TRP TRP 29/01/04 00:04:13 SYSDATE + 1/288 N delete TBL_PS_A_LOG where
    TBL_PS_A_LOG);

    18 TRP TRP TRP 29/01/04 00:04:16 SYSDATE + 1 N PS_Alerts_Cleaning();
    19 TRP TRP TRP 29/01/04 00:15:28 SYSDATE + 1 N PS_Alerts_Cleaning();
    20 TRP TRP TRP 29/01/04 00:19:01 sysdate + 1/24 N dbms_refresh.refresh('"TR
    21 TRP TRP TRP 29/01/04 00:19:01 sysdate + 1/24 N dbms_refresh.refresh('"TR
    22 TRP TRP TRP 29/01/04 00:19:02 sysdate + 1/24 N dbms_refresh.refresh('"TR
    23 TRP TRP TRP 29/01/04 00:19:03 sysdate + 1/24 N dbms_refresh.refresh('"TR
    24 TRP TRP TRP 29/01/04 00:19:04 sysdate + 1/24 N dbms_refresh.refresh('"TR

    JOB log_usr priv_usr schema LAST_DATE NEXT_DATE INTERVAL fails BR WHAT
    ----- ---------- ---------- ---------- ----------------- ----------------- --------------- ----- --
    25 TRP TRP TRP 29/01/04 00:19:05 sysdate + 1/24 N dbms_refresh.refresh('"TR

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Platform and version?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Sorry,i've not mentioned it:
    Platform - HP-UX B.11.11 U 9000/800 539760581 unlimited-user license
    version - 8.1.7.4

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do you see SNP0, SNP1 etc. processes at the o/s level?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    errors in alert.log, bdump?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    also I would create a simple job that fires immediately to verify it was working.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi guys,

    There are SNP processes at O/S level,the alert.log is clean,no trace files in udump.
    I'll try to create a new job and trace for it.
    Now i'm at home and tomorrow i'm working at different place,so i'll try it on Tuesday and i'll let you know.
    Meanwhile,i'll be glad to have more ideas.

    Thanks in advance.
    Nir

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You might try "resetting" the job queue by ...
    Code:
    alter system set job_queue_processes = 0;
    ... then wait a few minutes and ...
    Code:
    alter system set job_queue_processes = 8; -- or whatever
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    what is OS date?

  10. #10
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    can you execute the jobs manually as the owner of the jobs and are you sure there are no snmp type trace files in the udump or bdump?

    steve
    I'm stmontgo and I approve of this message

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