We have a 'newish' 19c database that was almost entirely populated by moving pluggable databases from our 12c instance to the new one.
These pluggables are working well.
A new pluggable was created on the 19c, this appeared to be fine and has worked well for an extended period of time until we tried to use some spatial functionality. We then discovered that the MDSYS objects were invalid:
OBJECT_NAME OWNER OBJECT_TYPE
__________________________ _________ _______________
SDO_INDEX_METADATA PUBLIC SYNONYM
USER_SDO_INDEX_METADATA PUBLIC SYNONYM
ALL_SDO_INDEX_METADATA PUBLIC SYNONYM
USER_SDO_INDEX_INFO PUBLIC SYNONYM
ALL_SDO_INDEX_INFO PUBLIC SYNONYM
SDO_JOIN PUBLIC SYNONYM
SDO_PQRY PUBLIC SYNONYM
USER_SDO_INDEX_METADATA MDSYS VIEW
ALL_SDO_INDEX_METADATA MDSYS VIEW
USER_SDO_INDEX_INFO MDSYS VIEW
ALL_SDO_INDEX_INFO MDSYS VIEW
MDPRVT_IDX MDSYS PACKAGE BODY
SDO_IDX MDSYS PACKAGE BODY
SDO_TUNE MDSYS PACKAGE BODY
SDO_UTIL MDSYS PACKAGE BODY
SDO_JOIN MDSYS FUNCTION
SDO_PQRY MDSYS FUNCTION
PRVT_PC MDSYS PACKAGE BODY
SDO_PC_PKG MDSYS PACKAGE BODY
We can't recompile these objects as this has to be done from the root container, but that container doesn't have any errors! If we do recompile the object in the root no errors are reported. However, if we then go to the pluggable "sho err" will show errors for the object.
What's going on and how can we fix it?!?