Unable to delete a feature class that had archiving enabled

Question

I am looking for information on how to delete a feature class from an enterprise geodatabase that appears to have become corrupted and is giving us errors with every action we take.  In the process of trying to replace a feature class with a new one, a user tried to delete a feature class that had archiving enabled and it failed to delete (archiving was not disabled first).  They then changed the name of the feature class so they could complete the replacement.  We have since tried to disable archiving then delete, however we get "DisableArchiving: ERROR 003644: Underlying DBMS error [ORA-00904: "GDB_TO_DATE": invalid identifier]".  When we try to delete the feature class (we tried right-click delete, and the delete geoprocessing tool), we get  "ERROR 000601: Cannot delete xyz. May be locked by another application.  Delete item [Error]: Underlying DBMS error [ORA-00904: "GDB_TO_DATE": invalid identifier]".  I get the same ORA-00904 error when attempting to view the attribute table.  There is also an associated feature xyz_H.  I believe this was created when they tried removing the archiving and I'm assuming selected to save the history.

Other actions were also taken in an attempt to fix the issue, but I do not know everything that was tried.  I believe the SDE tables got hosed up during this process.

I am hoping to find a procedure to cleanly delete the feature class manually from SDE.  Or a way to fix the "GDB_TO_DATE" error so that we can delete the feature through ArcGIS Pro.  This is a production database, so we are trying to avoid causing any further issues with any other data.

We are using ArcGIS Pro 3.2.  The enterprise geodatabase is version 10.9.1, Oracle version 19.19.0.0.

Appreciate any assistance.  I have been searching but cannot find a thorough answer that fits our issue.

Thanks!
Kim

anbinh · Answer

Hi Kim,

Did you manage to delete the feature class in the end?

My client is facing similar issue. They have a feature class that was once archiving enabled, but somehow it seems to have been corrupted because the archiving fields [GDB_FROM_DATE, GDB_TO_DATE, GDB_ARCHIVE_OID] do not exist in this feature class anymore. ArcGIS Pro still thinks this feature class as "archived", and we are having trouble with both disabling archiving and deleting this feature class.

Thanks,

MMBR · Answer

You need to work with your Oracle Database Administrator to capture an Oracle Trace when you try to delete the Featureclass. This will show more details, the Oracle Trace captures all SQL Statements been executed and any errors as well, you also need to capture the SDE intercept.
How To: Set up an SDEINTERCEPT Log File on a Client Machine for ArcGIS Desktop and ArcGIS (esri.com) 
This will help Esri Technical Support to identify the problem.

"DisableArchiving: ERROR 003644: Underlying DBMS error [ORA-00904: "GDB_TO_DATE": invalid identifier]"

ORA-00904: "GDB_TO_DATE"

ORA-00904 - Database Error Messages (oracle.com)

"The identifier or column name entered was invalid."

You can use Oracle SQL Developer to check the fields in the Featureclass base table, A table ( if registered as traditional versioning), the Archiving Table and the Multi-versioned View (_EVW View).

It is complaining about the GDB_TO_DATE field, then check the archiving fields.

Log in as the SDE user in Oracle SQL Developer, run the following query to get a list of columns recorded in the system table.

SELECT * FROM COLUMN_REGISTRY WHERE TABLE_NAME = '<feature_class_name>';

Compare the list with the fields shown in the feature class properties dialog box in ArcGIS Pro and with the list of fields shown by Oracle SQL Developer when describe the featureclass base table columns.

KimCloud · Answer

Thank you for the reply, unfortunately that did not help with our issue.  The feature is not currently used in any services and we tried with a brand new pro project with no other open connections.  We are unable to remove the archiving due to the "DisableArchiving: ERROR 003644: Underlying DBMS error [ORA-00904: "GDB_TO_DATE": invalid identifier]" error.  We get that error when right-clicking on the feature and unchecking the archiving box, as well as using the disable archiving geoprocessing tool.  We're going to open a ticket with esri unless we find any other fix for this issue.