My Oracle Support Banner

HOW TO RECREATE X$DIAG_xxxx OBJECTS ? ( ORA-00942, ORA-04063) (Doc ID 2095513.1)

Last updated on OCTOBER 08, 2019

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


Using DBUA to upgrade to but _diag_adr_enabled was set to FALSE instead of TRUE has introduced the error ORA-00942 when querying the ADR views.

After upgrade the parameter _diag_adr_enabled was reset to TRUE and the steps from documents:

<Note 1243837.1> - Upgrading From 11GR1 To 11GR2, DBUA Fails With ORA-00942 on X$DIAG_ADR_CONTROL
<Note 1674422.1> - 'ORA-04063: View "SYS.V_$DIAG_VIPS_PACKAGE_MAIN_INT" Has Errors' During Upgrade

were executed, but without fixing the issue:

CATADRVW.SQL or CATPROC.SQL or CATUPGRD.SQL always report the same errors :
09:29:43 SQL> create or replace view
09:29:43 2 v_$diag_ADR_CONTROL as select * from x$diag_ADR_CONTROL;
v_$diag_ADR_CONTROL as select * from x$diag_ADR_CONTROL
ERROR at line 2:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.07
09:29:43 SQL> create or replace public synonym
09:29:43 2 v$diag_ADR_CONTROL for v_$diag_ADR_CONTROL;

Synonym created.

Elapsed: 00:00:00.08
09:29:43 SQL> grant select on v_$diag_ADR_CONTROL to dba;
grant select on v_$diag_ADR_CONTROL to dba
ERROR at line 1:
ORA-04063: view "SYS.V_$DIAG_ADR_CONTROL" has errors

and same type of errors for all the v_$diag_xxxxx, x$diag_xxxxx objects : ORA-00942 followed with ORA-04063

Even after running utlrp all the views V_$DIAG_xxxxx are invalid because x$diag_xxxxx objects do not exist .




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

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