试试这个
对于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
);