Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it.

Since I'm truncating a different partition than the ones I'm intending to query, I expect this to work. I suspect a rogue query plan, doing PARTITION LIST (ALL) where I want (ITERATOR) or (JOIN-FILTER).

The query is complex, joining multiple partitioned tables.

So my question is: can I get any more detail out of Oracle to figure out precisely which table (or index) in the query is causing the problem? And is there any way I can capture the query plan that was in effect when the error occurred? (which could be different than the plan if I look at it right now)

share|improve this question
add comment

2 Answers 2

You should be able to get some of this information using a ServerError Trigger.

share|improve this answer
    
will this let me capture the specific table that caused the error, and/or the query plan that was in effect at the time? not just the full query. –  wrschneider99 Jul 23 '12 at 13:54
    
You can get the query, error stack, and back trace - not everything you would like, but perhaps a start. –  Leigh Riffel Jul 23 '12 at 14:03
add comment

How intermittently are you getting the error?

Do you have any logging in the trigger that is truncating a partition? You could use a simple procedure that writes (in an autonomous transaction) some information about which partition is being truncated.

Also create the on servererror trigger as suggested by Leigh Riffel, and log the query and error stack.

Finally, I would enable tracing of all occurrences of the long running query (assuming this query isn't executed all too often!):

alter session set sql_trace = true;
alter session set tracefile_identifier = wrschneider08103;

before the query and

alter session set sql_trace = false;

after.

Then, once the error has again reared its ugly head, run tkprof on the last trace file with 'wrschneider08103' in its name...

Hopefully you'll be able to correlate the information you've thus collected.

share|improve this answer
    
Thanks, I do know the partition being truncated. sql_trace may be what I'm looking for, so I can figure out whether there's any partition list (all) happening –  wrschneider99 Jul 26 '12 at 15:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.