Cannot Recompile Invalid SYS Objects for ORACLE_OCM.MGMT_DB_LL_METRICS (Doc ID 2495283.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

On :  11.2.0.4 version, RDBMS

Invalid SYS objects cannot be recompiled.

The issue started with an alert log messages:
    ORA-04063: package body "SYS.DBMS_SQLTUNE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE".

When the DBA tried to recompile this package body (as sysdba), it complained that a number of sys views were invalid.
When DBA checked, it was found that many SYS views were invalid, particularly those starting with DBA_HIST_.

DBA tried running utlrp, but it did not acknowledge that any invalid objects existed; however, a select from dba_objects indicates errors.
Compiling individually did not solve the issue: the object would not compile.

SQL>alter view sys.DBA_HIST_DATABASE_INSTANCE compile;
Warning: View altered with compilation errors.


SQL>alter view sys.dba_hist_snapshot compile;
Warning: View altered with compilation errors.

SQL>show errors
No errors.

SQL>alter view sys.dba_sqlset_STATEMENTS compile;
Warning: View altered with compilation errors.

SQL>show errors
No errors.
 
ERRORS
ORA-04063,ORA-06508

STEPS

1. Launch SQL Plus:

2. Check Errors.

SQL>show errors;
Errors for PACKAGE BODY ORACLE_OCM.MGMT_DB_LL_METRICS

LINE/COL ERROR
483/3 PL/SQL: SQL Statement ignored
483/41 PL/SQL: ORA-00942: table or view does not exist

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.