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?
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?
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 :
Where :x is the job number.
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)
What is your nls_date_format set.
NLS_DATE_FORMAT = "DD-MON-YYYY HH24:MI:SS"
change the nls_date_format to
or any year 2000 setting.
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!)
[QUOTE][i]Originally posted by gpsingh [/i]
[B]change the nls_date_format to
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.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.
Click Here to Expand Forum to Full Width