Oracle中重建表的全局的索引、分区索引、及同时建全局和分区索引----脚本

在Oracle中重建表的全局索引、分区索引，以及同时重建全局和分区索引的脚本可以如下编写：

BEGIN
    FOR idx IN (SELECT index_name, table_name FROM all_indexes WHERE owner = 'YOUR_SCHEMA_NAME' AND table_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_SCHEMA_NAME') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
    END LOOP;
END;
/

请将YOUR_SCHEMA_NAME和YOUR_TABLE_NAME替换为实际的模式名和表名。这段PL/SQL代码会遍历指定模式和表的所有索引，并对每个索引执行重建操作。

如果你想重建全局和分区索引，可以分别对全局索引和分区索引进行遍历和重建。

-- 重建全局索引
BEGIN
    FOR idx IN (SELECT index_name, table_name FROM all_indexes WHERE owner = 'YOUR_SCHEMA_NAME' AND table_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_SCHEMA_NAME' AND partitioned = 'N') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
    END LOOP;
END;
/
 
-- 重建分区索引
BEGIN
    FOR idx IN (SELECT index_name, table_name FROM all_indexes WHERE owner = 'YOUR_SCHEMA_NAME' AND table_name = 'YOUR_TABLE_NAME' AND owner = 'YOUR_SCHEMA_NAME' AND partitioned = 'YES') LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD PARTITION ' || 'ALL'; -- 或者指定分区
    END LOOP;
END;
/

在这个例子中，第一个PL/SQL代码块重建所有全局索引，第二个代码块重建所有分区索引。如果需要重建特定分区，可以在EXECUTE IMMEDIATE语句中指定分区名。