Oracle在线重建固定表空间下的索引

SELECT 
    'ALTER INDEX ' || i.owner || '.' || i.index_name || ' REBUILD TABLESPACE APPS_TS_QUEUES;' AS rebuild_command
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%'
    AND i.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
    AND i.partitioned = 'NO'
    AND s.tablespace_name = 'APPS_TS_SUMMARY'
union
SELECT 'ALTER INDEX ' || p.index_owner || '.' || p.index_name || 
       ' REBUILD PARTITION ' || p.partition_name || ' TABLESPACE APPS_TS_QUEUES;' 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'
    -- 【新增条件】只选择位于 APPS_TS_SUMMARY 表空间的索引分区
    AND s.tablespace_name = 'APPS_TS_SUMMARY'
;