Clean up orphaned records in the SDE repository tables

[HildegardGerlach1]

Hello,

I have orphaned records in the SDE repository tables (e.g. GDB_OBJECTCLASSES) which I cannot see from ArcCatalog any more.
I tried to do what is written here
http://support.esri.com/en/knowledgebase/techarticles/detail/34050

create the table with SQL (Oracle), I cannot create it in ArcCatalog as the table name exists;
but I cannot see the table from ArcCatalog afterwards to delete it

We have ArcSDE 9.3, and I use ArcCatalog 10.1
I tried as well to delete the table with ArcSDE 9.3 client sdetable -o delete, but the table name still remains in the SDE repository tables

extract from prerequisite check for upgrading SDE (although it is a warning)

Checking dataset (GISPRD.A4).
Table (GISPRD.A4) was found in GDB_ObjectClasses, but does not exist in the database.  Skipping.
DBMS table not found [GISPRD.A4][STATE_ID = 19914]
The index passed was not within the valid range.

Any help ?

Thanks

Hilde

[VinceAngelo]

'sdetable' won't populate or maintain SDE.GDB_* tables, only ArcGIS will.

Use SQL*Plus or SQLDeveloper (or your UI of choice) to create the table for re-DROPping.
The syntax is just "CREATE TABLE tblname ( OBJECTID INTEGER NOT NULL )".  Then you
can use Desktop to delete it again.

- V

[HildegardGerlach1]

I had tried that and it didn't work.
But then this morning I created again a table with SQLPlus and finally I saw it in ArcCatalog and could delete it.

Now finally I wanted to this for all tables which are not existing and have entries in GDB_OBJECTCLASSES.
But the strange thing is again, I cannot see the tables in ArcCatalog after having them created in SQLPlus. Even after reconnecting several times. But in Oracle the tables are there. I am using ArcGIS Desktop 10.1.

[VinceAngelo]

It's possible to remove SDE.GDB_* rows directly, but this should only be done
with close guidance from Tech Support.  I'd recommend you create an incident.

- V

[MarkChilcott]

One option is to use sdetable -o delete to delete the tables, then use ArcCatalog to create a table of the same name, then use ArcCatalog to delete it.

[Asrujit_SenGupta]

This won't work always, as if there are orphaned records, ArcGIS won't allow creation of a new Table with the same name.

So creating the Table using SDE-Command line or from DBMS end and then using ArcCatalog to delete it will remove all orphaned records.

[Mary_AnnRaymundo]

I had a similar issue where a dataset (SDE) could not be registered because a feature class that was renamed suddenly disappeared in ArcCatalog. Reading this thread was very helpful and decided to ask our DBA to add the orphaned table in SQL. Some people commented that the table does not appear in ArcCatalog and would appear the following day. While others have said that you have to refresh the SDE connection or re-add the connection.

As expected I could not see the newly added table in ArcCatalog even after refreshing the SDE connection. I had to delete the current SDE connection and add it again using the same credentials.

The table appeared in ArcCatalog and I was able to delete the table. Strange thing though is that the SDE dataset that I wanted to register was already registered as a version but could not edit a feature class ( it says I don't have the permission). I unregistered the dataset and registered it back and finally I was able to edit the feature class.