Oracle 断片化解消用スクリプト(Shrink)

データが頻繁に変更されるテーブル(INSERT、DELETE、UPDATE)について
定期的にShrinkすることで、断片化の解消ができます。

SET SERVEROUTPUT ON;

DECLARE
    skip_flag            boolean := false;
    /****************************************************************/
    /*  ローカル関数： SHRINK処理可能かチェック                     */
    /****************************************************************/
    function local_can_shrink(c_dba_tables in dba_tables%ROWTYPE) return boolean is
        cnt_mview            number;
        cnt_col              number;
        cnt_ind              number;
    begin
        -- クラスタ表であるか確認。
        if (c_dba_tables.cluster_name <> '') then
            return false;
        end if;

        -- 圧縮表であるか確認。
        if (c_dba_tables.compression = 'ENABLED') then
            return false;
        end if;

        -- ON COMMITマテリアライズド・ビューのマスター表であるか確認。
        select count(mv.mview_name) into cnt_mview from dba_mviews mv join dba_mview_detail_relations mvd on (mv.mview_name = mvd.mview_name)
            where mvd.detailobj_owner = c_dba_tables.owner and mvd.detailobj_name= c_dba_tables.table_name
        and mvd.DETAILOBJ_TYPE = 'TABLE' and mv.REFRESH_MODE ='COMMIT';
        if (cnt_mview > 0) then
            return false;
        end if;

        -- LONG列が含まれているか確認。
        select count(column_name) into cnt_col from dba_tab_columns
            where table_name = c_dba_tables.table_name and owner = c_dba_tables.owner
        and data_type = 'LONG';
        if (cnt_col > 0) then
            return false;
        end if;

        -- ファンクション・ベース索引、またはビットマップ結合索引であるか確認。
        select count(join_index) into cnt_ind  from dba_indexes
            where table_name = c_dba_tables.table_name and owner = c_dba_tables.owner
        and (index_type like 'FUNCTION-BASED%' or  (index_type = 'BITMAP' and join_index ='YES'));
        if (cnt_ind > 0) then
            return false;
        end if;

        return true;
    end;

BEGIN
    DBMS_OUTPUT.PUT_LINE('=== テーブルセグメント縮小処理開始:' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'));
    /* テーブルセグメント縮小 */
    FOR CUR IN (
       SELECT * FROM DBA_TABLES WHERE OWNER = 'DB_OWNER' AND TABLE_NAME IN (
         'DB_TABLE1'
        ,'DB_TABLE2'
       )
    ) LOOP
        -- SHRINK処理可能かチェック
        skip_flag :=local_can_shrink(CUR);
        -- 処理できない場合、スキップする。
        if (skip_flag = false) then
		goto label_continue;
        end if;

        -- 行管理の有効化
        execute immediate 'alter table '|| CUR.OWNER || '.' || CUR.TABLE_NAME ||' enable row movement';
        -- セグメントの縮小
        execute immediate 'alter table '|| CUR.OWNER || '.' || CUR.TABLE_NAME ||' shrink space';
        -- 行管理の無効化
        execute immediate 'alter table '|| CUR.OWNER || '.' || CUR.TABLE_NAME ||' disable row movement';
        -- アナライズ
        execute immediate 'analyze table ' || CUR.OWNER || '.'|| CUR.TABLE_NAME ||' estimate statistics sample 5 percent';
<<label_continue>> null;

    END LOOP;
    DBMS_OUTPUT.PUT_LINE('=== テーブルセグメント縮小処理終了:' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'));
END;
/

EXIT;