Tuesday, June 3, 2008

DBMS_STATS becomes INVALID after Refresh/Database Upgrade.

Issue:
******

One of the DBMS JOB failed and recorded error in the alertlog as

From ORCL database alert log file /ORCL/ORCLDB/10.2.0/admin/ORCL_ctloraerp06/bdump/alert_ORCL.log

9:ORA-12012: error on auto execute of job 282253
10:ORA-04063: ORA-04063: package body "SYS.DBMS_STATS" has errors


Observation:
************


SYS.DBMS_STATS Package Body is Invalid.

select object_name,object_type ,owner, status from dba_objects where object_name='DBMS_STATS';

OBJECT_NAME OBJECT_TYPE OWNER STATUS
-------------------- ------------------- ------------------------------ -------
DBMS_STATS PACKAGE SYS VALID
DBMS_STATS PACKAGE BODY SYS INVALID
DBMS_STATS SYNONYM PUBLIC VALID

Solution
*********


Rebuild/ Re-compile the DBMS_STATS Objects as

connect / as sysdba

@?/rdbms/admin/dbmsstat.sql
@?/rdbms/admin/prvtstas.plb
@?/rdbms/admin/prvtstat.plb