Thanks for the question, Parixit.
Asked: November 18, 2015 - 11:26 pm UTC
Last updated: November 19, 2015 - 5:05 am UTC
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Viewed 10K+ times! This question is 
You Asked
First of all, thanks for creating this forum and making it interesting.
We have a list-list partitioned table -let's say table_fact. The table is shared by multiple applications but these applications are loading the data in different partitions. We have a stored procedure which creates/truncates the partitions on this table during each load.
Now, we are getting 'ORA-14766:Unable to obtain a stable metadata snapshot' error on random basis while refreshing a materialized view built on table_fact table or while doing Qlikview (reporting tool) refresh which reads the data from table_fact. Both Materialized view and Qlikview will read data specific to the desired application only. The reads are hitting the partitions of the desired application only. The issue is usually coming when the table is being loaded by other application in a partition A and mat view or Qlikview is reading the data from partition B. There is a possibility that the partition creations/truncation happening on the table while the Mat view or Qlikview refresh are in progress- reading data from different partitions. It's worth pointing out that The reading and writing/truncating are NOT happening at the same time on the same partitions, it will only on different partition.
When we re-run the Mat view/Qlik view refresh it gets completed without any issues. All the literature I went through says to re-run the sql, I am more interested in understanding the root cause here. Thanks.
and Connor said...
Here's the catch - when you say:
"The reads are hitting the partitions of the desired application only"
the error is not saying, we need to know which partitions to read, its the preliminary step, ie, we need to get a list of partitions *in order* to work out what we are going to need. And we're struggling to do it, because things are changing. eg, when you say:
"The issue is usually coming when the table is being loaded by other application in a partition A and mat view or Qlikview is reading the data from partition B", then for all we know, you are in the middle of (say) adding partition C which might have an impact on things. *You* know that you are not, but the *database* does not know.
We error out because if we assumed wrongly, then we'd potentially return incorrect results.
I'd be looking at doing some manual serialisation to avoid the issue. For example, a dbms_lock call, or something simpler, ie, catch-error-and-retry.
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment