Catalog — Only show items that exist in the database (despite GDB system table artifacts)

[Bud]

ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB:

Anyone who's worked in Catalog with enterprise geodatabase data (especially in a dev environment) knows that sometimes items get broken. There are likely multiple possible causes. It's not necessarily due to a bug; someone might have accidentally deleted the table or view from the database. (not a big deal in a dev environment)

But the bottom line is, an item appears in Catalog, but the table or view doesn't actually exist in the database. The item shows up in Catalog because there are still records in the geodatabase system tables that pertain to the item.

The database view doesn't exist in the database (SQL Developer):

Deleting the item via Catalog has no effect. The item momentarily disappears from Catalog, but then it reappears when Catalog is refreshed.

Idea:

Could that behavior be changed so that Catalog only shows items that actually exist in the database? Or give us a tool to remove broken registered items from the geodatabase system tables?

Additionally, it would be great if that error message could be improved to say something like, "The item does not exist in the database".

[MarceloMarques]

@Bud - this can be easily fixed.

1. create a dummy table with the same name of the table that is missing using a SQL Create Table statement, any columns can be added to the dummy table.

2. Close ArcCatalog

3. Open ArcCatalog

4. Connect to the Oracle Geodatabase as the data owner user of the Featureclass / Table

5. In ArcCatalog delete the orphan Featureclass or Table from the Geodatabase. 

6. This removes the records from the ArcSDE Geodatabase Repository Tables.

Note: this is a well-known situation for many decades, and the recommendation is to only use ArcGIS to delete Geodatabase objects and never drop any table directly in the RDBMS because it leaves behind records in the Geodatabase ArcSDE Repository Tables and this causes the Geodatabase to be in a corrupted state, and geodatabase functionality like "sde compress" among other features will not work as expected and weird error messages might show up.

[SSWoodward]

Thanks for the idea @Bud.

Creating or dropping geodatabase tables trough the DBMS is not a supported workflow. 

Performing these types of operations risks corruption of the geodatabase and is not recommended. 

[Bud]

@SSWoodward This idea isn't so much about tables as it is about database views. I think most people would agree with me that views sometimes break in Catalog, for a variety of reasons. They're fragile in Catalog. The only reason I'd drop an item is to drop a view that is broken in Catalog. Sometimes, that actually works, it stops showing up in Catalog after I drop a view. This is especially applicable for unregistered views.

I think this idea is valid. 

[MarceloMarques]

@Bud - if ArcGIS Pro Catalog cannot open the view because for example the view is invalid because of dependencies, or any other situation involving dependencies and invalid objects, then what I have done to all my Oracle Databases is to monitor "invalid objects", if a table is dropped, or a field changes in a table and that causes the view to become invalid, then I have created an oracle dbms_scheduler job that executes every hour, the job executes a store procedure, and if it finds any invalid objects then it sends an email alert. You can find this alert in my database template scripts for Oracle in the link below.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

I hope this helps.