The DataPump expdp fails with the ORA-39126, ORA-31642, ORA-06512, ORA-06550, ORA-00201 errors.
Problem : The datapump expdp fails with the following errors.
Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS [] ORA-31642: the following SQL statement fails: BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.02.00'); END; ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_METADATA", line 1282 ORA-06550: line 1, column 8: PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.KUPW$WORKER", line 8398 ----- PL/SQL Call Stack ----- object line object handle number name 0x129b5cd68 19225 package body SYS.KUPW$WORKER 0x129b5cd68 8430 package body SYS.KUPW$WORKER 0x129b5cd68 12793 package body SYS.KUPW$WORKER 0x129b5cd68 2548 package body SYS.KUPW$WORKER 0x129b5cd68 9099 package body SYS.KUPW$WORKER 0x129b5cd68 1690 package body SYS.KUPW$WORKER 0xf2bbb830 2 anonymous block Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS [] ORA-31642: the following SQL statement fails: BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.02.00'); END; ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_METADATA", line 1282 ORA-06550: line 1, column 8: PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.KUPW$WORKER", line 8398 ----- PL/SQL Call Stack ----- object line object handle number name 0x115de4448 19225 package body SYS.KUPW$WORKER 0x115de4448 8430 package body SYS.KUPW$WORKER 0x115de4448 12793 package body SYS.KUPW$WORKER 0x115de4448 2548 package body SYS.KUPW$WORKER 0x115de4448 9099 package body SYS.KUPW$WORKER 0x115de4448 1690 package body SYS.KUPW$WORKER 0x15e113630 2 anonymous block Job "SYS"."EXP_JOB_201706061112" stopped due to fatal error at 11:13:11
Root Cause : The Data Mining schema DMSYS has been dropped in Oracle 11g. If it does, you should drop the DMSYS schema and its associated objects from the database. Because, Oracle migrated all DMSYS related object to SYS.
Solution : Check for existence of schema DMSYS and related object in the database after upgrading to Oracle 11g and drop them. Once dropped, try running DataPump expdp command.
SQL> SET LINES 220 PAGES 4999 SQL> SELECT * FROM SYS.exppkgact$ WHERE SCHEMA='DMSYS'; PACKAGE SCHEMA CLASS level# ------------------------------ ------------------------------ ---------- ---------- dbms_dm_model_exp dmsys 2 2000 dbms_dm_model_exp dmsys 3 4000 dbms_dm_model_exp dmsys 6 1000 SQL> DELETE FROM SYS.exppkgact$ WHERE SCHEMA = 'DMSYS'; 3 ROWS deleted. SQL> SQL> COLUMN db_link FOR a30 SQL> SELECT * FROM dba_synonyms WHERE table_owner='DMSYS'; OWNER synonym_name table_owner table_name db_link ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ PUBLIC cluster_rule_type dmsys cluster_rule_type PUBLIC cluster_type dmsys cluster_type PUBLIC odm_attribute_importance_model dmsys odm_attribute_importance_model PUBLIC odm_naive_bayes_model dmsys odm_naive_bayes_model SQL>DROP PUBLIC SYNONYM cluster_rule_type ; SYNONYM dropped. SQL>DROP PUBLIC SYNONYM cluster_type ; SYNONYM dropped. SQL>DROP PUBLIC SYNONYM odm_attribute_importance_model ; SYNONYM dropped. SQL>DROP PUBLIC SYNONYM odm_naive_bayes_model ; SYNONYM dropped. SQL> SELECT COUNT(*) FROM dba_objects WHERE OWNER='DMSYS'; COUNT(*) ---------- 0 SQL>
Note : You can create Dynamic SQL to perform the drop the above Public Synonyms.
References
- Datapump Export Fails with ORA-39126 ORA-31642 ORA-06512 ORA-06550 PLS-00201 ORA-06550 Errors (Doc ID 1564521.1)
- NO DMSYS Schema In 11G And Above And How To Remove IT (Doc ID 1497250.1)
- Upgrading Oracle Data Mining