Diagnosing an ORA-01410 error with a 10046 trace

As some of you will remember I have been chasing an intermitent ORA-01410 error which shows up in our data warehouse reports. Oracle tech support suggested that a 10046 trace would show me which table or index was causing the problem. I was hoping that I could take this information and correlate it with the log files for the ETLs which run at the same time as the reports. This would, I had hoped, allow me to prove to my management that the errors were caused by running reports while the ETL was truncating tables and dropping and recreating indexes.

Now Oracle tells me that the only useful information in the trace is the query. Since I already new from the end user what the query looked like, this does not help much.

Since the trace file gives me sequential list of all of the i/o operations that were done to execute the query (e.g. WAIT #1: nam='db file sequential read'...) I would expect that something in the file would show me where it had choked on the invalid rowid. In one of the traces, for example, the line in the file immediately before the ORA-01410 message is:

WAIT #1: nam='db file sequential read' ela= 4 p1=6 p2=210458 p3=1

Would the file and block numbers (p1 and p2) give me the this information or is this the last successful i/o operation?

If the latter, would I find the information that I am looking for elsewhere in the trace?

Thanks,
Peter Schauss

--
http://www.freelists.org/webpage/oracle-l