DMSYS.DBMS_MODEL_EXP

Database Data Pump Export fails with PLS-00201 identifier DMSYS.DBMS_MODEL_EXP must be declared [ID 304449.1] Applies to: Oracle Data Mining - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2] Information in this document applies to any platform. Checked for relevance on 27-Jun-2012. Symptoms The Database Data Pump export (expdp) utility reports : 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 Changes The DMSYS account or DMSYS package objects have been removed or have invalid status. Cause DMSYS objects have Invalid status or DMSYS schema objects have been accidentally removed or Step 'DELETE from exppkgact$ WHERE SCHEMA='DMSYS'; ' from Note:297551.1 has not been performed Solution If DMSYS schema has already been dropped: Start SQLPlus. Connect with user SYS as SYSDBA and issue the following commands: SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS'; SQL> exit; Proceed with export jobs. If DMSYS schema objects have been accidentally removed / have invalid objects: Set ORACLE_HOME and ORACLE_SID Start SQLPlus and connect with user SYS as SYSDBA If Database is version 10.1.0.x: 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 If Database is version 10.2.0.x: 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 Ensure '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'; Proceed with export jobs. Note : If database is version 10.2 the DMSYS user may not be removed.