RE: Tracing an ORA-01410 error
Date: Fri, 06 Feb 2009 15:32:39 +0100
a truncate on a table cannot be done while any select is active on it (DDL Lock). (I'm argueing it is the same for index ddl but have not testet).
But I know it is possible to do a complete refresh on a materialized view using truncate (must use atomic_refresh => TRUE since oracle 10) while select's are active on it because I used this as a feature some years ago just to overcome the impossibility of table truncation during active select's.
And, of course, materialized views are not unusual in data warehouses.
This may limit the searching for a problem solution to less objects.
> This is a data warehouse application which I inherited. They run an ETL
> every hour and the ETL takes about 70 minutes (don't ask). Then they
> run reports while the ETL is running. Some of the reports are failing
> intermittently with ORA-01410. I need something to prove conclusively
> that the problem either is or is not related to the fact that the ETL is
> truncating tables and dropping and recreating indexes.
> Interestingly, the errors only occur when the report is scheduled
> automatically. When it is manually immediately after a failure we don't
> get the error.
... some rows deleted ...