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’);