topic Re: Clean up orphaned records in the SDE repository tables in Data Management Questions

Clean up orphaned records in the SDE repository tables

HildegardGerlach1 — Sun, 12 Dec 2021 05:20:12 GMT

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

Re: Clean up orphaned records in the SDE repository tables

VinceAngelo — Wed, 05 Dec 2012 14:00:30 GMT

'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

Re: Clean up orphaned records in the SDE repository tables

HildegardGerlach1 — Thu, 06 Dec 2012 13:42:04 GMT

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.

Re: Clean up orphaned records in the SDE repository tables

VinceAngelo — Thu, 06 Dec 2012 15:08:54 GMT

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

Re: Clean up orphaned records in the SDE repository tables

MarkChilcott — Mon, 05 Dec 2016 03:53:29 GMT

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.

Re: Clean up orphaned records in the SDE repository tables

Asrujit_SenGupta — Mon, 05 Dec 2016 04:52:34 GMT

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.

Re: Clean up orphaned records in the SDE repository tables

Mary_AnnRaymundo — Tue, 08 Jan 2019 23:42:41 GMT

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.