RE: Diagnosing an ORA-01410 error with a 10046 trace
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <peter.schauss@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 May 2009 23:21:07 +0300
This IS very likely due truncates, alter table moves, partition exchanges,
drops (and subsequent space reuse) while some queries are running.
Again, you don't need to guess, either create a DDL trigger (after
ALTER,DROP,TRUNCATE which logs all DDLs) or just keep monitoring
last_ddl_time and data_object_id's of all the segments and see whether the
segments the query accesses change.
The "invalid rowid" error message doesn't necessarily indicate that there's
some corruption or such, but rather that a-pre truncate/drop index is used
for fetching rowids in a (long)running query and when accessing the table
segment using that rowid we discover that oops that block has been reused by
some other data object id (even if it's a new incarnation of the same
segment after truncate) - thus the table block access fails and oracle
returns a generic error message "invalid rowid".
I have seen I think only one case of "invalid rowid" which was due an actual
corruption, all others have been about someone doing truncates/DDL while
others query the data.
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Schauss,
> R. Peter (IT Solutions)
> Sent: 08 May 2009 21:43
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Diagnosing an ORA-01410 error with a 10046 trace
>
> (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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
