Problem: Invalid Objects and ST_GEOMETRY Operators Failure after Revoking Upgrade Privileg

Description

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"

or

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

Cause

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

SELECT ANY TABLE

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

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Invalid objects and ST_GEOMETRY operators failure after revoking upgrade privileges

Description

Cause

Solution or Workaround

Discover more on this topic