DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: dbms_job

  1. #1
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Folks,

    We have an Oracle job that runs once per week to run analyse on a number of tables. The job runs over the weekends and on a Monday when we look at the job it is always BROKEN and the next run time is set for the year 4000? Are there any error logs/tables that we can look at to see where it is going wrong. The job has run successfully before and no changes have been made to it. Any ideas?

    Thanks,

    Fraze

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Some additional info - the job being run is dbms_utility.analyze_schema('SAM','COMPUTE')

    Is there any way of getting errors out od dbms_utility?

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    Try running the job manually and you should be able to get an idea of what is going on. My guess is it's probably a privileges problem...

    You can run it like this :

    Begin
    DBMS_JOB.RUN(:x);
    End;

    Where :x is the job number.

    -John



  4. #4
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    John,

    That wouldn't solve my problem of getting error messages to see where and why its falling over would it?
    We also have the problem that its a live system so we cant run it on that envirment during the day (we do have a UAT on ethough)

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    What is your nls_date_format set.

  6. #6
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    NLS_DATE_FORMAT = "DD-MON-YYYY HH24:MI:SS"

  7. #7
    Join Date
    Feb 2001
    Posts
    389
    change the nls_date_format to
    DD-MON-RR
    or any year 2000 setting.

  8. #8
    Join Date
    Nov 2000
    Posts
    344
    Hi Fraze,

    Well, it might not tell you exactly what is going on, but it might give you a clue.

    Maybe the owner of the job does not have access to DBMS_utlility. Or maybe not the privileges to analyze the schema that you want to analyze. Remember that privileges granted through roles are not visible from stored procedures, so you may need to grant analyze on the schema directly to the owner of the job. (simplest to keep it all under one user!)


    -John

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by gpsingh [/i]
    [B]change the nls_date_format to
    DD-MON-RR
    or any year 2000 setting. [/B][/QUOTE]

    ???????

    1. DD-MON-YYYY HH24:MI:SS is the most "year 2000 compatible" date setting of them all, certanly much more than DD-MON-RR
    2. Date format has nothing to do with this kind of problem
    3. Mybe you suspected the date format problem because the next_date for the broken job is set at 01-01-4000? This is perfectly normal, it is not an error. When a job can not be executed without an unhandlede error, Oracle tries to execute it again after 1 minute, then again after 2 minutes, then after 4 minutes etc... It tries to execute it up to 16 times, if it fails after this 16th attempt, it marks it as broken. And all the broken jobs get their next_date set at 01-01-4000

    The jobs obviously atrts to run, but gets broken somewhere in the middle, and after 16 attempts it becomes broken. There could be many reasons why the job doesn't finish. Jdorlon suspects privileges, my guess would be insuficient temporary tablespace (analyze is very temp-space consuming operation).

    I'm almost certain that the event of unsuccessful job execution is recorded in alert.log with the pointer to appropriate trace file, where the reason of the problems can be found.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Nov 2000
    Posts
    245
    Q: does this job work before? when it start fail?
    when job is broken it will shown like this:
    JOB LOG_USER LAST_DATE LAST_SEC NEXT_SEC INTERVAL
    ---- ---------- --------- -------- -------- -----------------------------------
    B TOTAL_TIME FAILURES
    - ---------- ----------
    100 SNAPADMIN 23-MAR-01 15:20:17 00:00:00 /*30:Secs*/ sysdate + 30/(60*60*24)
    Y 5657 16

    the only place you can see the error is alert and trace file.

    find out the error first before do any changes.

    p.s you can try to run it using dbms_job.run(job#) from the job owner account.

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