Diagnosing an ORA-01410 error with a 10046 trace
- From: "Schauss, R. Peter (IT Solutions)" <peter.schauss@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 May 2009 13:43:14 -0500
(Oracle 8.1.7.4)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
