Error: ORA-04061: existing state of package body "SDE.ST_GEOMETRY_OPERATORS" has been invalidated
The following error messages appear together when an object or package is invalid. The name of the body may vary depending, specifically, on what is invalid.
ORA-04061: existing state of package body "SDE.ST_GEOMETRY_OPERATORS" has been invalidated
ORA-04065: not executed, altered or dropped package body "SDE.ST_GEOMETRY_OPERATORS"
Objects can become invalid during an ArcSDE upgrade or service pack installation when objects or packages are upgraded or replaced. Because of Oracle's object dependency model, when objects contain references to other objects or packages, other objects or packages that contain a dependency on the objects being modified during the installation are marked as invalid. Oracle sometimes recompiles invalid database objects automatically when referenced, but there are still cases when they are not automatically compiled.
Solution or Workaround
The status of all objects owned by the SDE user can be checked by executing the following SQL statement when connected as the SDE user in SQL*Plus:
SQL> SELECT object_name, object_type
WHERE STATUS = 'INVALID';
All the objects in the SDE schema can be recompiled by executing the Oracle 'sys.utl_recomp.recomp_serial' package.
- As the SYS user in SQL*Plus, execute the following command to grant privileges on the 'sys.utl_recomp' package to the SDE user:
SQL> GRANT EXECUTE ON utl_recomp TO sde;
- As the SDE user, execute the following command in SQL*Plus:
SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');
- After executing the sys.utl_recomp.recomp_serial command, execute the above SQL statement to discover any invalid objects. The results should not return any objects, since the status of all objects owned by SDE will be 'VALID'.