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 sysdba3. Run export jobs.
SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS';
SQL> exit;
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/ii) If Database is version 10.2.0.x do the following steps:
SQL> run $ORACLE_HOME/dm/admin/odmpatch.sql (if the database is at a patch level)
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> run $ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP $ORACLE_HOME/rdbms/admin/3. Ensure that 'Oracle Data Mining' is at valid status in dba_registry using,
SQL> run $ORACLE_HOME/rdbms/admin/odmpatch.sql
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql
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