Как изменить все индексы в схеме Oracle в одном script (10G)

Мне нужно добавить новый столбец ко всем индексам схемы Oracle, потому что база данных начнет хранить историю и имеет новое поле даты. Поскольку существующие индексы уникальны, мне нужно отбросить их все и создать их снова, но новые индексы включают новый столбец. Это было бы довольно легко, если бы все индексы, в которых один столбец, основанный на использовании системы, выглядели примерно так:

select 'CREATE INDEX ' || index_name || ' ON ' || table_name || ' (' || column_name || ',NEWCOLUMNNAME)' 
from sys.all_ind_columns 
where table_owner = 'SCHEMA_OWNER'

но, очевидно, я спрашиваю, потому что не все мои индексы основаны на одном столбце. Это то, что мне нужно выполнить в среде IDE, такой как TOAD. Версия базы данных - 10 г.

1 ответ

Попробуйте это

Для 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
);

Для Oracle 10g вы можете использовать:

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
 );

Для справки - Список различных методов агрегации строк

licensed under cc by-sa 3.0 with attribution.