Friday, July 16, 2010

Export DataPump fails with ORA-39125 while Calling DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT

Problem Description
While invoking oracle datapump schema level export it fails with error
"ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT" like below.
#> expdp userid=system/password DIRECTORY=mydir DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott

Export: Release 10.2.0.1.0 - 64bit Production on Sunday, 27 July, 2008 10:00:38

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."JOB1": userid=system/***** DIRECTORY=mydir DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS
while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-31642: the following SQL statement fails:
BEGIN DMSYS.DBMS_DM_MODEL_EXP.SCHEMA_CALLOUT('SCOTT',0,1,'10.01.00.02.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 872
ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called

Cause of the Problem
As the line "ORA-04063: package body "DMSYS.DBMS_DM_UTIL" has errors" appears the problem happened due to invalid state of the package DMSYS.DBMS_DM_UTIL. Package DMSYS.DBMS_DM_UTIL is used by the Oracle Data Mining option. You can verify the invalid objects in oracle by following query,
SQL> set lines 200
SQL> select status,
object_id,
object_type,
owner||'.'||object_name "OWNER.OBJECT"
from dba_objects
where status != 'VALID'
order by 4,2;

Solution of the Problem
Step 01: Log in to database as dmsys user and run the script dmutil.plb to re-create the invalid package.
SQL> CONNECT dmsys/dmsys
Connected.

SQL> @$ORACLE_HOME/dm/admin/dmutil.plb
Package created.
Package created.

Step 02: Run the script $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile the invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Step 03: Delete the Export DataPump log file and dump file of the previous failed attempt. Then re-run the export DataPump operation.

No comments:

Post a Comment