Oracle 12c EXPDP eror ORA-21700 ORA-06512 ORA-06512

while upgrading Oracle database from version 11.2.0.3 to 12.1.0.1 successfully, i found out the below errors while performing a full database export for the new upgraded 12cR1 database:

ERROR at line 1:

ORA-21700: object does not exist or is marked for delete

ORA-06512: at “SYS.ANYDATA”, line 174

ORA-06512: at “SYS.DBMS_SCHEDULER”, line 3583

why is this happening 😐  ??!

The reason i am facing this error is that Oracle Data Mining Modules (ODM) are not hosted in a separate schema “DMSYS” anymore when upgrading from 11.2.0.x to 11.2.0.3 or even from 10g  !! (which should have been dropped while upgrading  My Mistake 😦   )

Remark: Data Mining models are implemented as data dictionary objects in the SYS schema

To Correct this , you have either 2 ways:

********* First solutions:

Drop the DMSYS schema in the 11g database.

SQL> CONNECT / AS SYSDBA;
SQL> DROP USER DMSYS CASCADE;
SQL> DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA = ‘DMSYS’;
SQL> SELECT COUNT(*) FROM DBA_SYNONYMS WHERE TABLE_OWNER = ‘DMSYS’;

* if the previous query returns results then perform the following:

SQL> SET HEAD OFF
SQL> SPOOL dir_path/DROP_DMSYS_SYNONYMS.SQL
SQL> SELECT ‘Drop public synonym ‘ ||'”‘||SYNONYM_NAME||'”;’ FROM DBA_SYNONYMS
WHERE TABLE_OWNER = ‘DMSYS’;
SQL> SPOOL OFF
SQL> @dir_path/DROP_DMSYS_SYNONYMS.SQL
SQL> EXIT;

*********  Second Solution:

perform the following in the upgraded 12c database.

connect / as sysdba
exec dbms_scheduler.drop_program(‘JDM_BUILD_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_EXPLAIN_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_EXPORT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_IMPORT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_PREDICT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_PROFILE_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_SQL_APPLY_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_TEST_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_XFORM_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_XFORM_SEQ_PROGRAM’);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s