在线重建索引，按大小降序排列

--重建普通索引，排除分区索引
set lines 200 pages 50000
SELECT *
FROM (
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD ONLINE NOLOGGING;' AS rebuild_command
FROM (
    SELECT 
        i.owner,
        i.index_name,
        s.bytes / 1024 / 1024 AS index_size_mb  -- 转换为MB
    FROM 
        dba_indexes i
    JOIN 
        dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
    WHERE 
        i.index_type NOT LIKE '%LOB%'  -- 排除LOB索引
        AND i.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')  -- 排除系统用户
        AND i.partitioned = 'NO'  -- 排除分区索引，只保留普通索引
    ORDER BY 
        s.bytes DESC  -- 按大小降序排列
)
ORDER BY 
    index_size_mb DESC
)
WHERE ROWNUM <= 50;

--重建分区索引
set lines 200 pages 50000
SELECT *
FROM (
  SELECT 'ALTER INDEX ' || p.index_owner || '.' || p.index_name || 
         ' REBUILD PARTITION ' || p.partition_name || ' ONLINE NOLOGGING;' AS rebuild_command
  FROM 
      dba_ind_partitions p
  JOIN 
      dba_segments s ON p.index_owner = s.owner
                     AND p.index_name = s.segment_name
                     AND p.partition_name = s.partition_name
  JOIN
      dba_indexes i ON p.index_owner = i.owner
                    AND p.index_name = i.index_name
  WHERE 
      i.index_type NOT LIKE '%LOB%'
      AND p.index_owner NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP')
      AND i.partitioned = 'YES'
  ORDER BY 
      s.bytes DESC
)
WHERE ROWNUM <= 50;