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

5
votes

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

The typical implementation works as follows:

  • List all objects
  • Generate drop statements for these objects

Oracle Spatial Indexes have been causing me a lot of grief though.

How can I reliably enumerate them in order to produce DROP INDEX xyz statements?

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

My current solution looks like this:

On XE:

  • DELETE FROM mdsys.user_sdo_geom_metadata
  • DELETE 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 */

On Oracle 10g:

  • DELETE FROM mdsys.user_sdo_geom_metadata
  • SELECT object_type, object_name FROM user_objects WHERE object_type = 'TABLE' and object_name not like 'MDRT_%$'
  • DROP *table_name* CASCADE CONSTRAINTS PURGE /* for all tables */

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

XE doesn't.

Both 10g and XE don't cascade the removal of the metadata in MDSYS.user_sdo_geom_metadata

2
sqldatabaseoracleoracle-xeoracle-spatial
how are you searching for them at the moment? – Matt
I've expanded my question to include my current solution (which unfortunately is a big hack). – Axel Fontaine

2 Answers

5
votes

I solved it by enumerating all SPATIAL indexes using 

select INDEX_NAME from USER_SDO_INDEX_INFO

And using INDEX_NAME to generate DROP statements like

DROP INDEX my_index
0
votes

on 10g, try the xxx_SDO_INDEX_yyy views or alternatively look for objects with type SPATIAL_INDEX. You could drop each of these first then drop the table. I don't know whether these exist on XE or not.