How to reliably list and remove all spatial indexes in Oracle?

Question

In my Flyway open source database migration project, I have a function that clears all objects in the current database schema without dropping the schema itself.

A typical implementation works as follows:

• List of all objects
• Generate statement statements for these objects

Oracle Spatial Indexes caused me a lot of grief.

How can I reliably list them to create xyz instructions on the DROP INDEX?

Note. This should work on both XE, 10g and 11g . All references in the MDSYS schema must be deleted.

My current solution looks like this:

In XE:

• REMOVE FROM mdsys.user_sdo_geom_metadata li>
• REMOVE FROM mdsys.sdo_index_metadata_table WHERE sdo_index_owner = USER
• SELECT object_type, object_name FROM user_objects WHERE object_type = 'TABLE'
• DROP * table_name * CASCADE CONSTRAINTS PURGE / * for all tables * /

In Oracle 10g:

• REMOVE FROM mdsys.user_sdo_geom_metadata li>
• SELECT object_type, object_name FROM user_objects WHERE object_type = 'TABLE' and object_name do not like 'MDRT _% $'
• DROP * table_name * CASCADE CONSTRAINTS PURGE / * for all tables * /

10g seems to cascade the removal of metadata in MDSYS.sdo_index_metadata_table and the removal of spatial index tables (MDRT_1234 $, etc.).

XE does not.

Both 10g and XE do not cascade metadata deletion in MDSYS.user_sdo_geom_metadata p>

Answer 1

I solved it by listing all the SPATIAL indices using

select INDEX_NAME from USER_SDO_INDEX_INFO 

And using INDEX_NAME to create DROP statements like

 DROP INDEX my_index 

Answer 2

on 10g, try xxx_SDO_INDEX_yyy or alternatively find objects of type SPATIAL_INDEX . You can delete each one first, and then drop the table. I don't know if they exist on XE or not.