Oracle Export Errors ORA-31693 ORA-29913 ORA-21700

While checking the export of status of one of my supported databases (11gR2), i found out the log file has the below errors:

ORA-31693: Table data object “SYSTEM”.”SCHEDULER_PROGRAM_ARGS” failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

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

To Resolve this error:

the error shows that you have dropped an object that has dependencies, so you need to re-create it and then drop it.

or directly drop the scheduler objects as shown below.

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

Ora-21700 Object Does Not Exist Or Is Marked For Delete When Dropping a User With ST_GEOMETRY

When trying to drop the SDE user, you are getting the error:

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

A corruption is introduced in your ArcSDE Geodatabase instance:

1. Try to drop user SDE using drop user cascade command: DROP USER SDE CASCADE;
This will generate error if there are objects created using SDE.ST_GEOMETRY because of dependencies

2. Try to drop the tables in other schemas using the SDE.ST_GEOMETRY type
Get also error ORA-21700: object does not exist or is marked for delete

3. Back to SDE and try to drop the SDE.ST_GEOMETRY type gives error
ORA-21700: object does not exist or is marked for delete 

The Cause of this issue:

Dictionary gets corrupted when tables using a specific object type (SDE.ST_GEOMETRY) are tried to be dropped after the type was tried to get dropped 

The Solution:

the objects cannot be dropped after this type of corruption is introduced

The objects can be left there and create a new database for this cartridge to be reinstalled or create a new database and do full exp/imp

In order to avoid this problem the objects using the SDE.ST_GEOMETRY type have to be dropped before dropping the SDE schema. Also the drop user SDE cascade should not be used.


ESRI Reference Link:

Oracle Metal ink reference document number 9876