Databases
StackOverflow2013
Postsdbo
POHow to reliably list and drop all spatial indexes in Oracle?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to reliably list and drop all spatial indexes in Oracle?
    text
    copied!<p>In my open-source database migration project <a href="http://code.google.com/p/flyway" rel="nofollow">Flyway</a>, I have a feature that cleans all objects in the current database schema, without dropping the schema itself.</p> <p>The typical implementation works as follows:</p> <ul> <li>List all objects </li> <li>Generate drop statements for these objects</li> </ul> <p><strong>Oracle Spatial Indexes</strong> have been causing me a lot of grief though.</p> <p>How can I <strong>reliably enumerate</strong> them in order to produce DROP INDEX xyz statements?</p> <p>Note: This must work on both <strong>XE, 10g and 11g</strong>. All references in the MDSYS schema must be gone.</p> <p>My current solution looks like this:</p> <p>On XE:</p> <ul> <li>DELETE FROM mdsys.user_sdo_geom_metadata</li> <li>DELETE FROM mdsys.sdo_index_metadata_table WHERE sdo_index_owner = USER</li> <li>SELECT object_type, object_name FROM user_objects WHERE object_type = 'TABLE'</li> <li>DROP *table_name* CASCADE CONSTRAINTS PURGE /* for all tables */</li> </ul> <p>On Oracle 10g:</p> <ul> <li>DELETE FROM mdsys.user_sdo_geom_metadata</li> <li>SELECT object_type, object_name FROM user_objects WHERE object_type = 'TABLE' and object_name not like 'MDRT_%$'</li> <li>DROP *table_name* CASCADE CONSTRAINTS PURGE /* for all tables */</li> </ul> <p>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).</p> <p>XE doesn't.</p> <p>Both 10g and XE don't cascade the removal of the metadata in MDSYS.user_sdo_geom_metadata</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload