English

Problem: Invalid objects and ST_GEOMETRY operators failure after revoking upgrade privileges

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 );