Oracle Spatial Is Invalid And Recompiling Invalid Objects Raises ORA-00904: "SDO_ROOT_MBR": Invalid Identifier (Doc ID 1370015.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 10.2.0.5 and later
Information in this document applies to any platform.
***Checked for relevance on 01-Nov-2013***

Symptoms

In Oracle Server 10.2.0.5 Oracle Spatial component is invalid in the registry and there are many MDSYS invalid objects:

OWNER                          OBJECT_TYPE         OBJECT_NAME
------------------------------ ------------------- ---------------------------------
MDSYS                          FUNCTION            SDO_JOIN
MDSYS                          FUNCTION            SDO_PQRY
MDSYS                          PACKAGE BODY        MD2
MDSYS                          PACKAGE BODY        SDO_3GL
MDSYS                          PACKAGE BODY        SDO_IDX
MDSYS                          PACKAGE BODY        SDO_RELATE_MASK
MDSYS                          PACKAGE BODY        SDO_ROUTER_PARTITION
MDSYS                          PACKAGE BODY        SDO_TUNE
MDSYS                          VIEW                ALL_SDO_INDEX_INFO
MDSYS                          VIEW                ALL_SDO_INDEX_METADATA
MDSYS                          VIEW                MY_SDO_INDEX_METADATA
MDSYS                          VIEW                SDO_RELATEMASK_TABLE
MDSYS                          VIEW                USER_SDO_INDEX_INFO
MDSYS                          VIEW                USER_SDO_INDEX_METADATA
PUBLIC                         SYNONYM             ALL_SDO_INDEX_INFO
PUBLIC                         SYNONYM             ALL_SDO_INDEX_METADATA
PUBLIC                         SYNONYM             MY_SDO_INDEX_METADATA
PUBLIC                         SYNONYM             SDO_INDEX_METADATA
PUBLIC                         SYNONYM             SDO_JOIN
PUBLIC                         SYNONYM             SDO_PQRY 
PUBLIC                         SYNONYM             SDO_RELATEMASK_TABLE
PUBLIC                         SYNONYM             USER_SDO_INDEX_INFO
PUBLIC                         SYNONYM             USER_SDO_INDEX_METADATA


The most frequent error shown while compiling the invalid objects is:
ORA-00904: "SDO_ROOT_MBR": invalid identifier

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms