When upgrading Spotlight schema to 8.0, the following error was received:
The Spotlight User Wizard has encountered an error. An error was reported for table "RecreatePMSnapshotJob" in connection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=sid)(INSTANCE_NAME=sid)))".
The error is:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SOO_USER.QUEST_PPCM_COLLECTOR" has been invalidated
ORA-04065: not executed, altered or dropped package body "SOO_USER.QUEST_PPCM_COLLECTOR"
ORA-06508: PL/SQL: could not find program unit being called: "SOO_USER.QUEST_PPCM_COLLECTOR"
ORA-06512: at line 7
in table RecreatePMSnapshotJob
After the user wizard completes, please run the following SQL statements to ensure that there are no invalid objects:
select object_name, object_type, status from dba_objects where object_name like 'QUEST_SOO%';
select object_name, object_type, status from dba_objects where object_name like 'QUEST_PPCM%';
If there are no invalid objects, no further action is needed. If invalid objects are returned, it is recommended to drop the Spotlight user with cascade option, and run the Oracle User Wizard again to re-create the objects.