ORA-01031 or ORA-00942

False ORA-01031 or ORA-00942 Raised for Objects with the Same Name in Different Schemas [ID 1287914.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Valid statements run by users with appropriate permissions are intermittently failing with ORA-00942 or ORA-01031.
What is specific to this kind of issues is that objects with the same name as the ones accessed by the query exist in other schemas.
To investigate the issue, the following events are set:

For ORA-01031:
alter system set events='1031 trace name errorstack level 3';
For ORA-942:
alter system set events='942 trace name errorstack level 3';

If the instance is restarted, the event setting statements above should be rerun.
After checking the generated trace files, the following errorstack is found:

kzpchkbu <- kksaxs <- kksauc <- kkscscid_auc_eval <- kkscsCheckCriteria
<- kkscsCheckCursor <- 449 <- kkscsSearchChildLis <- kksSearchChildList <- kksfbc
<- kkspsc0 <- kksParseCursor

To have an issue of this kind investigated by support, set the following event and upload the resulting trace into the SR:

alter system set events '1031 trace name errorstack level 3; name library_cache level 72';

Cause

There are several known causes for these errors:
BUG 9577583 FALSE ORA-942 OR OTHER ERRORS WITH MULTIPLE SCHEMAS HAVING IDENTICAL OBJECTS
The fix for BUG 9577583 is apparently exposing a similar issue:
Bug 13080778 ORA-00942 RETURNED IN SQL ON MULTIPLE SCHEMAS DATABASE
consequently, if the Patch 9577583 does not completely fix the errors, it is likely to hit as well  bug 13080778.

Another bug with the same behavior is:
BUG 10165083 PROCEDURE TRIES TO WRITE TO A WRONG SCHEMA (CHILD CURSORS MISMATCH) IN 10.2.0.5

Solution

The workaround for the problem is to flush the shared pool:

alter system flush shared_pool;

This is to be used until Patch 9577583 or any merge including it can be installed on the database side.
Please check:
Note 9577583.8 Bug 9577583 - False ORA-942 or other errors when multiple schemas have identical object names
for the releases where this bug is permanently fixed.
When Patch 9577583 does not completely fix the problem, the Patch 13080778 should also be requested and installed.
Patches for:
Bug 13080778 ORA-00942 RETURNED IN SQL ON MULTIPLE SCHEMAS DATABASE
can be requested on the supported 11.1.0.7, 11.2.0.2 and 11.2.0.3 releases.
This bug is permanently fixed in 12.1

BUG 10165083 - PROCEDURE TRIES TO WRITE TO A WRONG SCHEMA (CHILD CURSORS MISMATCH) IN 10.2.0.5
Is permanently fixed in 11.2.0.3 and 12c. Patches exist for lower releases and can be still requested for 11.2.0.2
Good to know merge numbers:
10.2.0.5 merge Patch 13593365 includes all 3 bug fixes above
11.1.0.7.0 merge Patch 13692717 includes Bug 9577583 and Bug 13080778 fixes
11.1.0.7.8 merge Patch 13486650 includes Bug 9577583 and Bug 13080778 fixes
11.1.0.7.10 merge Patch 13705058 includes Bug 9577583 and Bug 13080778 fixes
11.1.0.7.11 merge Patch 13918125 includes Bug 9577583 and Bug 13080778 fixes
11.1.0.7.12 merge Patch 14258915  includes Bug 9577583 and Bug 13080778 fixes
11.1.0.7.13 merge Patch 14757779  includes Bug 9577583 and Bug 13080778 fixes
11.2.0.1.6 merge Patch 14741163 includes Bug 9577583 and Bug 13080778 fixes
PSUs including the bug fixes:
Bug: 10165083
PSU - 11.2.0.2.5
PSU - 10.2.0.5.6
PSU - 11.1.0.7.10
Bug 13080778
PSU - 11.2.0.2.8
PSU - 11.2.0.3.4