ORA-04061: existing state of package body "SDE.ST_GEOMETRY_OPERATORS" has been invalidated

Error Message

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"

Cause

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

Code:
SQL> SELECT object_name, object_type
       FROM USER_OBJECTS
      WHERE STATUS = 'INVALID';

1. As the SYS user in SQL*Plus, execute the following command to grant privileges on the 'sys.utl_recomp' package to the SDE user:
   
   

   Code:
   SQL> GRANT EXECUTE ON utl_recomp TO sde;

   
   
2. As the SDE user, execute the following command in SQL*Plus:
   
   

   Code:
   SQL> EXECUTE sys.utl_recomp.recomp_serial('SDE');

   
   
3. 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'.