Problem: 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,
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 );
Last Published: 5/5/2016
Article ID: 000010957