How to alter all indexes on an Oracle schema in one script (10G)

Question

I need to add a new column to all indexes of an Oracle schema because the database will start storing history and has a new date field. Since existing indexes are unique, I need to drop them all and create them again but have the new indexes include the new column. This would be fairly easy if all indexes where single-column based using the system views with something like this:

select 'CREATE INDEX ' || index_name || ' ON ' || table_name || ' (' || column_name || ',NEWCOLUMNNAME)' 

from sys.all_ind_columns 

where table_owner = 'SCHEMA_OWNER'

but obviously I'm asking because not all my indexes are single-column based. This is something that I need to execute on an IDE like TOAD. The database version is 10g.

Answer 1

Try this

For Oracle 11g:

SELECT 'CREATE INDEX ' || INDEX_NAME || ' ON ' 
        || TABLE_NAME || ' (' || COL_LIST  || ',NEWCOLUMNNAME)' 
FROM ( 
       Select index_name,table_name,
       LISTAGG(COLUMN_NAME || DECODE(DESCEND,'ASC','', ' ' || DESCEND), ',') 
       WITHIN GROUP (ORDER BY COLUMN_POSITION) AS COL_LIST 
       FROM SYS.ALL_IND_COLUMNS
       GROUP BY index_name,table_name
);

For Oracle 10g you can use:

SELECT 'CREATE INDEX ' || INDEX_NAME || ' ON ' 
     || TABLE_NAME || ' (' || COL_LIST  || ',NEWCOLUMNNAME)' 
FROM ( 
       SELECT INDEX_NAME,TABLE_NAME,            
       REGEXP_REPLACE(XMLAGG (XMLELEMENT(E, COLUMN_NAME || 
       DECODE(DESCEND,'ASC','', ' ' || DESCEND) ||',') 
       ORDER BY COLUMN_POSITION).EXTRACT('//text()'), '\s*,\s*$', '') AS  COL_LIST 
       FROM SYS.ALL_IND_COLUMNS
       GROUP BY INDEX_NAME,TABLE_NAME
 );

For Reference - List of different string aggregation techniques