oracle 脚本 索引,如何在一个脚本中更改Oracle模式的所有索引(10G)

试试这个

对于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

);