Oracle unusable index rebuild

--批量重建分区表索引 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';