How to Drop All the Indexes in Oracle Table

Hi Guy's,

We can drop all the indexes in a single procedure.


SQL> select table_name,index_name,status from dba_indexes where table_name='SHSHTDTA';

TABLE_NAME     INDEX_NAME                     STATUS
---------- --------------------------------------------------------------------
SHSHTDTA         SHSHTDTA_PK                      VALID
SHSHTDTA         IX_PANIC                                VALID
SHSHTDTA         IX_SHDATE                             VALID
SHSHTDTA       SHSHTDTA_INDX                     VALID


SQL>  BEGIN
      FOR ind IN
      (SELECT index_name FROM user_indexes WHERE table_name='SHSHTDTA' AND index_name NOT IN
      (SELECT unique index_name FROM user_constraints WHERE table_name='SHSHTDTA' AND index_name IS          NOT NULL))
      LOOP
      execute immediate 'DROP INDEX '||ind.index_name;
      END LOOP;
      END;
        /

PL/SQL procedure successfully completed.


SQL> select table_name,index_name,status from dba_indexes where table_name='SHSHTDTA';
no rows selected




Happy Reading...