RE: job fails
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jkstill@xxxxxxxxx>, "'Brian Zelli'" <Brian.Zelli@xxxxxxxxxxxxxxx>
- Date: Tue, 25 Feb 2014 12:51:25 -0500
In addition to what Jared has proposed, you make be able to locate from the
data dictionary the last object with updated stats.
Then if you can noodle out what the next table is, you might do a select
count(rightmost_unindexed_column) from <tab>
and/or just do a stats gather on that next table.
The other thing you could do is generate the list of gather stats call for
the schema and execute them each individually.
I trust all your keepalive layers to the client are on. Then again, jobs
like this may be best done directly on the server.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jared Still
Sent: Tuesday, February 25, 2014 12:13 PM
To: Brian Zelli
Cc: oracle-l (oracle-l@xxxxxxxxxxxxx)
Subject: Re: job fails
On Thu, Feb 20, 2014 at 12:33 PM, Zelli, Brian <Brian.Zelli@xxxxxxxxxxxxxxx>
wrote:
I'm running a dbms_stats.gather_schema_stats job thru a unix job and it runs
for a while then fails with a
ORA-01013: user requested cancel of current operation
I didn't cancel the job so why is this stopping?
I've had to jump through a few hoops before to find out why dbms_stats
doesn't work.
You might want to try this to start:
Wrap the call to dbms_stats in a PL/SQL procedure.
Use the EXCEPTION clause to trap errors, then capture the error stack and
timestamp of the failure
and save in a table, or write to the alert log.
Then find out what else was happening at that time that may have stopped
your job.
If that doesn't help, then you may want to include a 10046 level 0 trace.
Level 0 doesn't not show waits or binds, but for this test it is not
important to capture those.
Minimizing the size of the trace file is important however, and it can be
large.
If that doesn't work, there is other tracing that can be done on dbms_stats
directly, but
that is another post in case this bit doesn't help.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Sr Oracle DBA at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
Other related posts:
