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:

BEGIN
    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
       )
    )
    LOOP
        execute immediate 'DROP INDEX '||ind.index_name;
    END LOOP;
END;

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.

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Read about our Cookie Policy.