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
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