오라클 로컬 파티션 인덱스 리빌드 PLSQL 프로시져

 

CREATE OR REPLACE PROCEDURE rebuild_partitioned_indexes(owner in varchar2,p_table_name IN VARCHAR2) 
IS
    v_owner varchar(200);
    v_index_name      VARCHAR2(200);
    v_partition_name  VARCHAR2(200);
    v_sql  VARCHAR2(2000);
BEGIN
    FOR idx IN (SELECT ip.index_name, ip.partition_name from DBA_IND_PARTITIONS ip
    inner join dba_part_indexes di on di.index_name = ip.index_name and ip.index_owner = di.owner 
    WHERE di.table_name = UPPER(p_table_name) and ip.index_name not like 'SYS%' and ip.index_owner = UPPER(owner)) 
    LOOP
        v_index_name := idx.index_name;
        v_partition_name := idx.partition_name;
        v_sql := 'ALTER INDEX ' || owner||'.'||v_index_name || 
                          ' REBUILD PARTITION ' || v_partition_name || ' online ' ;
        DBMS_OUTPUT.PUT_LINE('Rebuilt sql: ' || v_sql);
        EXECUTE IMMEDIATE v_sql;
        
        DBMS_OUTPUT.PUT_LINE('Rebuilt index partition: ' || v_index_name || ' - ' || v_partition_name);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('All partitions rebuilt for table: ' || p_table_name);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END rebuild_partitioned_indexes;