Friday, July 16, 2010

Expdp fails with PLS-00201: identifier DMSYS.DBMS_DM_MODEL_EXP must be declared

Problem Description
Oracle data pump export fails with error "PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared ORA-06550:" like below.
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('CLUS1PROD1',0,1,'10.01.00.03.00');
END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 872
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Cause of the Problem
The problem arises because DMSYS schema objects have been accidentally removed or DMSYS schema objects have Invalid status or DMSYS schema has been dropped.

Solution of the Problem
Case 01: DMSYS has been dropped
1. Start SQLPlus.
$ sqlplus /nolog

2. Connect with user SYS as SYSDBA and issue the following commands:
SQL> conn / as sysdba
SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS';
SQL> exit;
3. Run export jobs.

Case 02: DMSYS schema objects have been accidentally removed / have invalid objects
1. Start SQLPlus and connect with user SYS as SYSDBA
$ sqlplus / as sysdba

2.
i) If Database is version 10.1.0.x do the following steps:
 SQL> run $ORACLE_HOME/dm/admin/dminst.sql SYSAUX TEMP $ORACLE_HOME/dm/admin/ 
SQL> run $ORACLE_HOME/dm/admin/odmpatch.sql (if the database is at a patch level)
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql
ii) If Database is version 10.2.0.x do the following steps:
 SQL> run $ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP $ORACLE_HOME/rdbms/admin/ 
SQL> run $ORACLE_HOME/rdbms/admin/odmpatch.sql
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql
3. Ensure that 'Oracle Data Mining' is at valid status in dba_registry using,
SQL> select COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Oracle Data Mining';

4. Run the export jobs.

No comments:

Post a Comment