Select your language

  • Davide Gammone


How to drop all indexes on an Oracle SQL table

Long time ago I needed to delete all indexes on an Oracle SQL table. After some test I ended up with the following PL/SQL code:

    FOR ind IN 
        SELECT index_name
        FROM user_indexes
        WHERE table_name = 'my_table'
        AND index_name NOT IN
            SELECT unique index_name
            FROM user_constraints
            WHERE table_name = 'my_table'
            AND index_name IS NOT NULL
        execute immediate 'DROP INDEX '||ind.index_name;

It drops all indexes which are not referenced by the constraints of the table. It is important, because the drop statement will fail if the table has a constraint created using the specified index. Just replace the 'my_table' string with the appropriate table name, If you want to use this code.

GDPR Alert

This site uses cookies, including third-party requests, to offer you services in line with your preferences. By closing this banner, scrolling this page or clicking any element, you consent to the use of cookies. If you want to learn more or opt out of all or some cookies, click on Privacy Policy...