My Oracle Support Banner

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

Last updated on JULY 02, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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