DBMS_METADATA.GET_DDL Fails with ORA-04063 Error

Symptoms

Call to dbms_metadata.get_ddl failed with following error –

ORA-04063: package body “SYS.DBMS_METADATA” has errors

expdp failed with following error –

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD [ESTIMATE_PHASE]
ORA-04063: package body “SYS.DBMS_METADATA” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_METADATA”

Cause

Tried to compile the package manually.

SQL> ALTER PACKAGE SYS.DBMS_METADATA COMPILE BODY ;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY SYS.DBMS_METADATA:

LINE/COL ERROR
——– —————————————————————–
2659/9 PL/SQL: SQL Statement ignored
2661/20 PL/SQL: ORA-04063: view “SYS.KU$_XMLSCHEMA_VIEW” has errors

Object dba_xmlschema_level_view is missing so KU$_XMLSCHEMA_VIEW can not be validated.

Solution

To resolve the issue reinstall the XDB.

XDB Removal

spool xdb_removal.log
set echo on;
connect / as sysdba
shutdown immediate;
startup
@?/rdbms/admin/catnoqm.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
set pagesize 1000
col owner format a8
col object_name format a35
select owner, object_name, object_type, status
from dba_objects
where status = ‘INVALID’ and owner = ‘SYS’;
spool off;

XDB Installation

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password

B. XDB user default tablespace

   * The SYSTEM, UNDO and TEMP tablespace cannot be specified.

   * The specified tablespace must already exist prior to running the script.

  
* A tablespace other than SYSAUX should be specified, especially if you
expect Oracle XML DB Repository to contain a large amount of data.

   * For example:

      create tablespace XDB
      datafile ‘xxxxxxxxx.dbf’ size 2000M
      extent management local uniform size 256K segment space management auto;

C. XDB user temporary tablespace

The syntax to run catqm.sql is the following:
SQL> @?/rdbms/admin/catqm.sql A B C

For example:
SQL> @?/rdbms/admin/catqm.sql xdb XDB TEMP

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

spool xdb_install.log
set echo on;
connect / as sysdba
shutdown immediate;
startup;
@?/rdbms/admin/catqm.sql
<XDB pwd> <XDB default tbs> <XDB temporary tbs> —
substitute the parameters with appropriate values
@?/rdbms/admin/catxdbj.sql
@?/rdbms/admin/utlrp.sql
spool off

  • January 22, 2019 | 24 views
  • Comments