CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user2428207
user2428207

Reputation: 825

Fetching Indexnames from Table with Cursor

I would like to fetch all existing indexes (Bitmap Index Joins) from a table with a cursor and drop them. Well and the other way around, if and index does not exists, create them.

So far I was doing this with a procedure:

DECLARE
  COUNT_INDEXES INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO COUNT_INDEXES
  FROM USER_INDEXES
  WHERE INDEX_NAME = 'IDXNAME' ;

  IF COUNT_INDEXES > 0 THEN
    EXECUTE IMMEDIATE 'DROP INDEX IDXNAME';
  END IF;
END;

Upvotes: 0

Views: 98

Answers (2)

Alen Oblak
Alen Oblak

Reputation: 3325

Here is a script, that drops all the indexes of a chosen table. For re-creating them, you have to provide some more information. Do you want to create a index on every column?

DECLARE
  l_table_name varchar2(20) := 'MY_TABLE'; 
BEGIN
  FOR r_idx IN
    SELECT INDEX_NAME
    FROM   USER_INDEXES
    WHERE  TABLE_NAME = l_table_name;
  LOOP
    EXECUTE IMMEDIATE 'DROP INDEX ' || r_idx.INDEX_NAME;
  END;
END;

Upvotes: 1

haki
haki

Reputation: 9779

here you go 

declare
    cursor c_idx is 
        select  index_name 
        from    user_indexes 
        where   table_name = 'my_table_name';
begin 
    for x in c loop 
        execute immediate 'drop index '|| x.index_name;
    end loop;
end;
/

Upvotes: 1

Related Questions