Upgrade From 12C To 18C (18.3) - ORA 942 Errors Are Observed

刘泽宇

现象：
After an upgrade to 18C, one of the pl/sql block is failing and throwing a 942 Error in one of the sql which is dynamically generating:
PARSE ERROR #140737245922752:len=207 dep=2 uid=63 oct=3 lid=63 tim=2418737549978 err=942<<<<
SELECT distinct TBL$OR$IDX$PART$NUM("ENTITY_.....) ORDER BY 1
about to signal 942


改动：
Database upgrade to 18C from 12C.

原因：
This is due to an internal bug:

Bug 29970587 - ORA-00942 WHILE RUNNING THE PROCEDURE AFTER UPGRADE TO 12.2.0.1
During the creation of TAB$, query current user is set as table owner and schema is also set as table owner so owner name is not attached to the table name in the query.


处理方法：
1. Bug is fixed in 20.1

2. Apply Patch 29970587

3. Or use following workaround:

ALTER SESSION SET "_fix_control"="20379571:0"

ALTER SESSION SET"_subquery_pruning_enabled"=false

ALTER SESSION SET "optimizer_features_enable"=12.1.0.2