Troubleshooting Oracle ORA-08103 - object no longer exists 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)
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.