5

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)

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • 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
    – wrschneider
    Jul 26, 2012 at 15:31
2

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

Improve this answer
2
  • 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.
    – wrschneider
    Jul 23, 2012 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, 2012 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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