Upgraded Oracle 19c eGDB from 11.1 to 11.4 using ArcGIS Pro 3.4 - Invalid Indexes / dbms_utility.compile_schema

[JamesMorrison1]

Hello Everyone

On our TEST system we upgraded Oracle 19c eGDB from 11.1 to 11.4 using ArcGIS Pro 3.4

After the upgrade we got a number of Invalid Indexes

SELECT * FROM DBA_OBJECTS WHERE STATUS!= 'VALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

OWNER             OBJECT_NAME           OBJECT_TYPE STATUS
MYDATA            A3992_IX1                      INDEX INVALID
MYDATA            A3993_IX1                     INDEX INVALID
MYDATA           A3994_IX1                     INDEX INVALID
MYDATA           V_PRINT_JOBS              VIEW INVALID

I did find this information: 

• DBA_OBJECTS is mostly for PL/SQL objects (procedures, views, etc.).
• Indexes can show up there, but they don’t require “compilation.”
• An index reported as invalid usually means it’s UNUSABLE or LOGGING anomalies — not that it’s corrupt.

However, the following query returned no rows

SELECT owner, index_name, status, tablespace_name FROM dba_indexes WHERE status = 'UNUSABLE'; 

So I ran:

EXEC dbms_utility.compile_schema( 'MYDATA', compile_all => FALSE );

SELECT * FROM DBA_OBJECTS WHERE STATUS!= 'VALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
No Rows Returned

Which seemed to fix the issue - I just do not understand why  dbms_utility.compile_schema would fix Indexes. Anyone help?

[AlisonWieckowicz · Accepted solution]

I believe it works because a spatial index is a function based index.  dbms_utility.compile_schema doesn't directly handle indexes, but it can indirectly impact them if the code defining the indexes (like function-based indexes) is invalid.