:

ORA-04045: errors during recompilation/revalidation of…

ORA-04045 Drop Perfstat User

The Problem

After re-compiling objects within the database I got the following error:

ORA-04045: errors during recompilation/revalidation of PUBLIC.STATS$PGA_TARGET_ADVICE
ORA-00980: synonym translation is no longer valid

You can use the following SQL to find out what the problems were.

SELECT *
FROM UTL_RECOMP_ERRORS;

The Cause

The PERFSTAT user may have been dropped without using the spdrop.sql script. Perhaps with a command like:

DROP USER PERFSTAT CASCADE;

The Solution

Re-run the ORACLE_HOME\rdbms\admin\spdrop.sql script which should clean up the synonyms and additional objects which are no longer required and will also be showing as invalid.

If you have dropped the PERFSTAT user which is the schema which houses the STATSPACK information, maybe you are using AWR reports to monitor the performance of your database,  which I have written more about.

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *