RE: job fails

From: Mark W. Farnham <>
Date: Tue, 25 Feb 2014 12:51:25 -0500
Message-ID: <164101cf3252$34a242f0$9de6c8d0$>

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.  


From: [] On Behalf Of Jared Still
Sent: Tuesday, February 25, 2014 12:13 PM To: Brian Zelli
Cc: oracle-l ( Subject: Re: job fails    

On Thu, Feb 20, 2014 at 12:33 PM, Zelli, Brian <> 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 Oracle Blog: Home Page:

Received on Tue Feb 25 2014 - 18:51:25 CET

Original text of this message