A customer called me up and said that he’s hitting “ORA-14766: Unable To Obtain A Stable Metadata Snapshot” and he’s coming up dry on his google search.
This was the first time I encountered this problem, so I thought I’d investigate and write a few words about it, because the problem seems it might be pretty common and the solution was a bit weird.
The customer is using 18.104.22.168 and has a table which is both partitioned and has a LOB in it (CLOB). Every once in a while – he’s not really sure why, he’s hitting ORA-14766.
When he runs the same query again (retry), without any changes to the database or the query, the same query works.
In the errorstack, we see something that looks like this:
----- Error Stack Dump ----- ORA-14766: Unable to obtain a stable metadata snapshot ----- Current SQL Statement for this session (sql_id=xxx) ----- Call stack shows lob function and kokl functions qesmaKokaInitLocalP <- kokliihdl <- koklglfn <- koklOutlineRead1 <- koklread1 <- kpolob <- opiodr <- ttcpip
So I actually googled it – and didn’t find anything relevant – even the Oracle documentation says:
ORA-14766: Unable to obtain a stable metadata snapshot Cause: This operation was not able to obtain a stable snapshot of the interval partitioning metadata. Action: Retry the statement.
So looking in MOS, I found a note about this issue. There is a note 2127653.1 describing the issues.
It appears that when using partitioned table which uses a LOB, and one session is causing an invalidation of the cursor – other sessions using the same cursor will fail.
The invalidation of the cursor is happening at the table-level granularity even if the invalidation happened at a lower level. Let’s use an example: if we’re adding adding a new interval partition and we have a query on the same table – that might cause the cursor to become invalidated. Even if the cursor has nothing to do with that new partition, it will still fail.
Adding a partition isn’t the only cursor invalidation reason out there – it can also happen because of flush of the shared pool or by collecting statistics (and others).
So in that sense, the ORA-14766 is an expected behavior in order to maintain consistency – but this means that some query might fail without prior notice and we’ll have to handle it.
Oracle recommends doing one of two things:
- Change the application to catch the ORA-14766 error and retry the query.
- Not run operations that cause cursor invalidations while the query is running (which is obviously not something we can control, over time).
Since this is not a bug, there is no patch for it – but Enhancement Bug 23064222 was filed so it will automatically retires the query and let’s hope it will be approved for development soon.
Please note – ORA-14766/ORA-14403 errors may be seen during concurrent partition maintenance operations (PMOPS), in the presence of deferred segment creation. This happens becuase couple of bugs which have related patches in the MOS. See here, for more details:
Both Bug 15866428 and Bug 13446912 are fixed for RDBMS 22.214.171.124 and 126.96.36.199 releases.
The Patch 15866428 and Patch 13446912 are available on top of some RDBMS releases and platforms.
If it needs these fixes on top of 188.8.131.52 release for others platforms, you should contact the support for a backport.
In any case, this was not the case with my customer.