Invalid objects and ST_GEOMETRY operators failure after revoking upgrade privileges


After upgrading an ArcSDE for Oracle geodatabase and revoking the upgrade privileges, the SQL access to spatial data using ST_GEOMETRY operators may fail with the following errors:

"ORA-29900: operator binding does not exist"

"ORA-06540: PL/SQL: compilation error"

"ORA-06553: PLS-907: cannot load library unit SDE.ST_GEOMETRY_OPERATORS"


"ORA-06553: PLS-907: cannot load library unit SDE.ST_GEOM_UTIL"


When this privilege is revoked from the SDE admin user after upgrading a geodatabase,


Oracle automatically marks some packages, functions, and triggers as invalid for auto revalidation based on the nature of the database objects. However, any ST_GEOMETRY operator that has a dependency on those invalidated objects, fails to compile with the errors above.

Solution or Workaround

The problem can be resolved by recompiling the geodatabase packages, triggers, and functions marked as invalid. This can be done with the following SQL command as the sysdba user. Repeat the same step for each multiple user schema geodatabase as necessary.

Exec dbms_utility.compile_schema( 'SDE', compile_all => FALSE );

    Article ID:000010957

    • Legacy Products

    Get help from ArcGIS experts

    Contact technical support

    Download the Esri Support App

    Go to download options

    Discover more on this topic