Deleting Oracle SDE features classes properly

Question

I have got a few feature classes in an SDE connection. This is the situation:

a) They are listed in ArcCatalog (10.1), but the open fails. The message says:

'Error opening feature class, DBMS table not found [ORA-04043: object XXX does not exist]'

b) Obviously, the tool Delete_management cannot be used as the geometry does not exist and it fails too.

c) If you go to your Oracle schema the individual tables corresponding to those geometries are not there, they have been deleted I am afraid and the whole thing makes sense. But under the SDE user, if you open the 'LAYERS' table, the table names are there.

I have read this post, but it does not help me: Delete SDE feature class features with ArcPy

So, in order to clean the database, shall I delete the entry in the LAYERS table and problem solved? what else or what other steps in Oracle are necessary? every time I open ArcCatalog the corrupted layers are listed and I want to remove them completely.

[1]: Delete SDE feature class features with ArcPy

Do not touch the geodatabase registry. The best way to handle this issue is to create a dummy table via SQL (CREATE TABLE xxx (objectid number (38) not null)) then use Desktop to delete the feature class. — Vince, Feb 22 '16 at 12:30
Creating the dummy table seemed to work, and then right click and delete in ArcCatalog, but when opening ArcCatalog again the SDE feature classes were listed there again, so they cannot be removed by this way. — Web-GIS entrepreneur, Feb 22 '16 at 14:18
They always have been removable by this procedure, which means your metadata is *profoundly* corrupted. It's time to talk with Tech Support, to see if they can un-FUBAR your instance. — Vince, Feb 22 '16 at 14:37

Web-GIS entrepreneur · Accepted Answer · 2016-03-03T12:06:35.060

I have researched that when you add a new SDE feature class through your Oracle connection in ArcCatalog, these are the SDE tables to be affected:

-SDE.COLUMN_REGISTRY
-SDE.GDB_FIELDINFO
-SDE.TABLE_REGISTRY
-SDE.GDB_OBJECTCLASSES
-SDE.GDB_FEATURECLASSES
-SDE.LAYERS
-SDE.GEOMETRY_COLUMNS

Actually you can also check the above list, if you have 'admin' privileges, by doing the following:

a) Type in Oracle:

exec dbms_stats.gather_schema_stats(ownname => 'SDE');
SELECT TABLE_NAME, OWNER, NUM_ROWS from all_tables WHERE OWNER = 'SDE' ORDER BY NUM_ROWS DESC;

Keep this info.

b) Upload a feature class to Oracle from ArcCatalog.

c) Do again step a) and check which tables have increased their number of rows.

Anyway, in my particular scenario, the corrupted fc instead of being missing from all the tables above, they were as records in two of them:

- SDE.LAYERS
- SDE.GEOMETRY_COLUMNS

Which explains why they were listed in ArcCatalog. So, I removed the entries in these two tables and problem solved.

In any other situation, the solution to deal with corrupted fc might be different, because there are dependencies and/or foreign keys associated amongst the SDE tables so I presume the order in which you delete or modify records matters.

In essence, if I open now ArcCatalog, the corrupted feature classes don't appear any more.

Deleting Oracle SDE features classes properly

1 Answers1