Fixing Invalid Oracle19c Spatial Objects During Patching

During patching, we faced an error of script md/admin/sdowfscs.sql

We are facing the error with following two components. Their objects remains invalid.

During patching we will find the following objects in invalid state as follows:

SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

OBJECT_NAME               OBJECT_TYPE             OWNER
------------------------- ----------------------- ----------
SDO_NET_PARTITION         SYNONYM                 PUBLIC
DRVDML                    PACKAGE BODY            CTXSYS
SDO_OLS                   PACKAGE BODY            MDSYS
SDO_WFS_PROCESS           PACKAGE BODY            MDSYS
SDO_WFS_LOCK              PACKAGE BODY            MDSYS
SDO_NET_PARTITION         PACKAGE                 MDSYS
SDO_NET_PARTITION         PACKAGE BODY            MDSYS
SDO_NET                   PACKAGE BODY            MDSYS
SDO_ROUTER_PARTITION      PACKAGE BODY            MDSYS
SDO_ROUTER_TIMEZONE       PACKAGE BODY            MDSYS
SDO_NDM_TRAFFIC           PACKAGE BODY            MDSYS
SPARQL_SERVICE_IMPL_T     TYPE BODY               MDSYS
SEM_RDFCTX_DR             PACKAGE                 MDSYS
RDFCTX_WS_EXTRACTOR       TYPE BODY               MDSYS
CALAIS_EXTRACTOR          TYPE BODY               MDSYS
CTX_ENTITY_EXTRACTOR      TYPE BODY               MDSYS
GATENLP_EXTRACTOR         TYPE BODY               MDSYS
SEM_RDFCTX_DR             PACKAGE BODY            MDSYS
SEM_RDFCTX_IR             PACKAGE BODY            MDSYS
PRLLIDX_CREATE            TYPE BODY               MDSYS
SEM_RDFCTX                PACKAGE BODY            MDSYS
SEMCONTEXTINDEXMETHODS    TYPE BODY               MDSYS
SEM_RDFCTX_OPER           PACKAGE BODY            MDSYS
S_SEM_RDFCTX_DR           PACKAGE BODY            MDSYS
W_SEM_RDFCTX_ALL          PACKAGE BODY            MDSYS
 
25 rows selected.

Solution:

To validate them, we need to assign following permission to user for fix the issue

GRANT EXECUTE ON SYS.UTL_HTTP TO CTXSYS;
GRANT EXECUTE ON SYS.UTL_HTTP TO MDSYS;
GRANT EXECUTE ON SYS.UTL_FILE TO MDSYS;
GRANT EXECUTE ON SYS.UTL_TCP TO MDSYS;

Run the UTLRP.SQL to make the objects valid.

@?\rdbms\admin\utlrp.sql

If you try to compile object by object and check error like

ALTER SYNONYM SDO_NET_PARTITION COMPILE;

 ORA-65040: operation not allowed from within a pluggable database

Note: You may get this ORA-65040 error that this operation is not done in PDB environment, do not confuse this is resolved by giving above permissions. If not solved then give DBA privileges to user and later revoke it.