ORA-04063 and ORA-00904 on “SYS.DBA_REGISTRY” Has Errors

Overview

If you’ve ever encountered the dreaded ORA-04063 and ORA-00904 errors when querying the DBA_REGISTRY view in your Oracle Database, you’re not alone.
These errors typically indicate corruption or invalidation within the SYS.DBA_REGISTRY view or the underlying DBMS_REGISTRY package — both essential components for tracking installed database components.

This issue can appear in Oracle Database versions 10.2.0.1 through 11.2.0.4, and may also impact Oracle Cloud and Exadata environments.

---

Symptoms

When querying DBA_REGISTRY, you may see the following error message:

SQL> SELECT * FROM dba_registry;

ORA-04063: View "SYS.DBA_REGISTRY" has errors

Attempting to recompile the view results in:

OWNER           NAME             TYPE   SEQUENCE  LINE  POSITION
--------------- ---------------- ------ -------- ------ --------
SYS             DBA_REGISTRY     VIEW   1         0     0

ORA-00904: "DBMS_REGISTRY"."SCHEMA_LIST_STRING": invalid identifier

This clearly points to a missing or invalid object inside the DBMS_REGISTRY package.

---

Cause

The root cause is that the DBA_REGISTRY view has become invalid.
When Oracle attempts to reference DBMS_REGISTRY.SCHEMA_LIST_STRING during compilation, it fails because the procedure or function no longer exists or is corrupted.

This can happen due to:

• Interrupted patch or upgrade processes
• Incomplete database installation scripts
• Invalid objects after a failed catalog or catproc execution
• Missing components in the SYS schema

---

Solution — Step-by-Step Fix

The simplest and most reliable fix is to recreate the DBMS_REGISTRY package and the DBA_REGISTRY view using Oracle’s standard catalog scripts.

Follow these steps carefully:

1. Connect as SYSDBA

sqlplus / as sysdba

2. Shutdown the database gracefully

SHUTDOWN IMMEDIATE;

3. Start in UPGRADE mode

STARTUP UPGRADE;

4. Re-run Oracle catalog and procedural scripts

These scripts rebuild system views, PL/SQL packages, and registry-related metadata.

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

• catalog.sql → recreates data dictionary views.
• catproc.sql → recompiles PL/SQL packages (including DBMS_REGISTRY).
• utlrp.sql → recompiles all invalid objects in the database.

5. Restart normally

SHUTDOWN IMMEDIATE;
STARTUP;

Once restarted, verify that the issue has been resolved:

SELECT * FROM dba_registry;

The query should now return all registered components without errors.

---

Verification

You can confirm the registry’s health by running:

SELECT COMP_ID, COMP_NAME, STATUS, VERSION FROM dba_registry;

If all components show VALID status — congratulations, your database registry is back in shape!

---

Best Practices to Prevent Recurrence

• Always run catalog and catproc scripts after a failed or partial upgrade.
• Ensure $ORACLE_HOME environment variables are correctly set before upgrades.
• Use utlrp.sql after patching or running datapatch.
• Regularly check for invalid objects using:

SELECT object_name, object_type FROM dba_objects WHERE status = 'INVALID';

---

Final Thoughts

The DBA_REGISTRY view plays a crucial role in Oracle Database maintenance, as it tracks installed components like XML DB, Oracle Text, Spatial, and more.
When it becomes invalid, it often signals deeper data dictionary issues — so resolving it promptly helps ensure database integrity and smooth patch management.

By following the steps above, you’ll not only resolve the ORA-04063 and ORA-00904 errors but also restore your Oracle registry to a clean, valid state.