Remove row from SDE.Table_Registry

[BlakeTerhune]

Our SDE (10.0 on Oracle 11g) has a tabular view created from a feature class and a table. The view is valid in Toad but it does not show up in ArcCatalog for either the schema owner or the SDE user; arcpy.Exists() returns False. There is, however, an entry in the SDE.Table_Registry for the view. How can I remove this view from the table registry?

[SamuelTompsett]

Hi Blake,

It would be best to contact ESRI Technical Support so they can verify the view is orphaned and can walk you through the process of removing all records in the repository tables.

-Sam

[BlakeTerhune]

Thanks. I'll do that and report back.

[BlakeTerhune]

I believe these orphaned table names in the Table_Registry were caused by dropping the table or view from Toad instead of ArcCatalog. After speaking with Esri support, they recommended cleaning this up by following these steps:

1. Identify "layer_id" for "table_name" in the SDE_LAYERS table
2. Identify "Registration_ID" for "table_name" in the SDE_TABLE_REGISTRY table
3. Identify "ID" for "Name" in the GDB_items table
4. Identify "g_table_name" for "f_table_name" in the SDE_GEOMETRY_COLUMNS table
5. Delete "f" and "s" tables for "g_table_name" found in Step 4. If "f" table is "f22" then "s" table is "s22".
6. Delete entry for "Name" in GDB_Items.
7. Delete entry for "f_table_name" in SDE_GEOMETRY_COLUMNS
8. Delete entry for "table_name" in SDE_LAYERS
9. Delete "i" table for "Registration_ID" (Step 4)
10. Remove all "table_name" entries in "SDE_COLUMN_REGISTRY".
11. Remove "table_name" entry in "SDE_TABLE_REGISTRY"

ArcGIS Help 10.2 - System tables of a geodatabase stored in Oracle

Basically, go through these six system tables and delete any rows that reference the object you are trying to delete.

• SDE.LAYERS
• SDE.TABLE_REGISTRY
• SDE.COLUMN_REGISTRY
• SDE.GDB_ITEMS
• SDE.GEOMETRY_COLUMNS
• SDE.COLUMN_REGISTRY

[RandyKreuziger]

In SQL Server if one of the database owners deletes a Geodatabase registered table or view with the SQL tools the same things happens but the fix is much cleaner.  Simply create a dummy table of the same name using the SQL tools (Management Studio) and then go in and delete the table/view using ArcCatalog. 

[AsrujitSengupta]

That is applicable for Oracle and other DBMSs as well. However, sometimes when that doesn't help...the above mentioned steps might be helpful.

[BlakeTerhune]

The Esri support technician did try this though. It makes sense and would be a logical first step.

[Arne_Gelfert]

A dusty thread but I still found this very helpful. While I recently completed one of the ESRI SDE classes to get more familiar with the data model, this was a good summary. I had one of those orphan table pop up in table_registry. As a check for others, I ran a quick:: 

select A.table_name, B.name
from SDE_table_registry A
left join
sys.tables B
on A.table_name = B.name 
where b.name is NULL‍‍‍‍‍‍

There can be any number of tables in your database (SQL Server in my case) but I'm thinking there shouldn't be anything in the registry tables that is not also in the system table.

[BlakeTerhune]

Great idea Arne Gelfert‌. Here's the equivalent for us Oracle folks.

select A.table_name
from SDE.table_registry A
left outer join ALL_TABLES B
    on A.table_name = B.table_name
where b.table_name is NULL‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍