Oracle unusable index rebuild

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

--批量重建分区表索引 UNUSABLE的分区索引都重建了 DECLARE   V_SQL  VARCHAR2(2000); BEGIN       FOR J IN (SELECT INDEX_NAME,PARTITION_NAME,STATUS,TABLESPACE_NAME               FROM USER_IND_PARTITIONS WHERE INDEX_NAME IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME IN (SELECT DISTINCT TABLE_NAME FROM USER_TAB_PARTITIONS))               AND STATUS  = 'UNUSABLE')   LOOP     EXECUTE IMMEDIATE 'ALTER INDEX '||J.INDEX_NAME||' REBUILD PARTITION '||J.PARTITION_NAME||' TABLESPACE ' || J.TABLESPACE_NAME;   END LOOP;     END;     SELECT INDEX_NAME,STATUS,'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE;' AS REBULD_NORM_SQL FROM USER_INDEXES T WHERE STATUS = 'UNUSABLE' UNION ALL SELECT INDEX_NAME,STATUS,'ALTER INDEX '||INDEX_NAME||' REBUILD PARTITION '||T.PARTITION_NAME||';' AS REBULD_PART_SQL FROM USER_IND_PARTITIONS T WHERE STATUS = 'UNUSABLE';