Problem: Dropping the SDE user in Oracle corrupts dependent tables with st_geometry attributes and spatial indexes
If the SDE user is dropped prior to removing all dependent tables with st_geometry attributes and spatial indexes, the tables are marked as invalid and become unusable.
For example, if the SYS or SYSTEM user drops the SDE user and then a session attempts to query or describe a table that contained an st_geometry attribute and spatial index, the Oracle error ORA-24372 is encountered.
The following example demonstrates encountering the ORA-24372 error, when attempting to describe a table in SQL*Plus, after the SDE user has been dropped.
SQL> DESCRIBE sewers
ORA-24372: invalid object for describe
If the table containing the st_geometry attribute does not contain a spatial index, the attribute is silently dropped by Oracle (resulting in data loss).
When the SDE user or st_geometry attribute type is dropped all dependent tables that contain an st_geometry attribute and an st_spatial_index are marked as invalid.
The tables are invalid because the dependent st_geometry type no longer exists.
Oracle currently prevents dropping an attribute that is referenced by a domain index. If dropping an attribute that is referenced by a domain index is attempted, Oracle raises an error as the following example demonstrates.
SQL> ALTER TABLE sewers DROP (shape);
ALTER TABLE sewers DROP (shape)
ERROR at line 1:
ORA-29884: domain index is defined on the column to be dropped
But when the st_geometry type or SDE user is dropped, Oracle ignores the error message encountered, when it attempts to drop the dependent table's attribute and proceeds to perform the DROP request.
Solution or Workaround
Either restore the Oracle instance from backup (restore both the SDE user and the schema with st_geometry attributes) or drop the impacted users from the Oracle instance.
It is not possible to drop the impacted invalid table.