ORA-08176: inconsistent reads with 11g interval partitioning
724067May 23 2011 — edited May 24 2011We are using a SQL MERGE to read data from one table and write it to a table that has interval partitions, but we are getting an ORA-08176 "consistent read failure" error when the process would have to create a new partition.
When the error occurs, it seems like the new partition has been created for the table and corresponding index (both on the same partition key), but the actual attempt to write to it via the MERGE is causing the error (the MERGE uses the partition key as one of its matching criteria).
However, if we run a "SELECT COUNT(*) FROM the_table" then try again, the MERGE succeeds.
Our theory is that it is something to do with the MERGE trying to check for data in the new partition before Oracle "knows" the partition/index are in sync. Running the SELECT presumably forces Oracle to run through the index and recognise the new partitions.
Anybody out there got any idea if this sounds plausible, and if so, how we can get around the problem (running SELECT COUNT(*) seems a bit clunky)?
Thanks for any help.
Chris