Hello,
Our production database has been upgraded from 11.2.0.4 to 12.1.0.2 months ago.
The application team was facing this error > ORA-08103: object no longer exists , while trying to generate several reports using the attached query.
I have checked the following:
They running the queries with the correct table(s) owner.
No corruption found with the database header block.
No accidental delete of the target table(s).
No data file I/O error with the alert log.
No corruption in UNDO tablespaces.
No disabled or offline indexes.
The db_file_multiblock_read_count:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 128
I have performed the following:
Run dbv on all data files in the DWH db and no corruption found with any data file.
Run utlrp.sql but still there invalid objects in SYS schema:
COUNT(*) OWNER
---------- ------------------------------
29 SYS
Nothing was in recyclebin & dba_recyclebin but I have purged anyway.
I was thinking to run these scripts > catalog.sql & catproc.sql (in case something wrong with the data dictionary) but I wasn’t sure whether it will be effective or not.
I have tried the following workaround and it’s working:
I have added this hint to the query > " /*+optimizer_features_enable=10.2.0.4*/ " and the query worked successfully without any errors but it took long time than usual.
Knowing that I have tried also to change the hint with > /*+optimizer_features_enable=12.1.0.2*/ but it didn't worked.
The current optimize value as following:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.2.0.4
Today, the application team has tested some of those queries and they told me that they worked but I think it’s not fixed permanently, I think these results were cached because of that 10g hint and the error will reproduce again.
I don't know if it’s a bug or not .
My questions are, if it’s not a bug why the query worked with the 10g optimizer features ? also, I don’t understand why we use the 11.2.0.4 optimizer features instead of the 12c as a workaround with the queries as long as some queries now worked with the 10g ?
Please advise.
Thanks