ある表領域内の複数表の断片化をまとめて解消する方法

はじめに

とある表の断片化を解消する際は、対象の表に対して下記のDDLを実行する必要があります。

alter table テーブル名 enable row movement;
alter table テーブル名 shrink space cascade;
alter table テーブル名 enable row movement;

STATSPACK用表領域TOOLSDAT内の表は、STATSPACKスナップショットを削除した際に
断片化してしまうことがよくある一方で、TOOLSDAT表領域内の表の一つ一つに
上記のコマンドを実行するのは面倒であるという課題がありました。

課題への対応策

以下のように、dba_segmentsから対象の表領域内の表名を取得し、
上記３つのalter文を一気に実行するPL/SQLを考えました。

defragmentation.sql

whenever sqlerror exit sql.sqlcode

set echo on
set verify off
set serveroutput on
set pages 1000 lines 1000

PROMPT
ACCEPT t_owner CHAR PROMPT 'Schema:';
PROMPT
ACCEPT ts_name CHAR PROMPT 'Tablespace:';
PROMPT

spool D:\basis_scripts\log\defrag.log
DECLARE

  t_name dba_tables.table_name%TYPE;

  -- サイズの降順に上位20件のテーブル情報を取得しています。

  CURSOR objects IS
  SELECT owner, segment_name, segment_type, bytes FROM dba_segments
  WHERE owner = '&&t_owner' and tablespace_name = '&&ts_name' AND segment_type = 'TABLE'
  ORDER BY 4 DESC
  FETCH FIRST 20 ROWS ONLY;

  -- 3つのalter文を格納するための変数

  enable_rowmove varchar2(1000);
  shrink_space varchar2(1000);
  disable_rowmove varchar2(1000);

BEGIN

  FOR obj_tab IN objects LOOP

    enable_rowmove := 'ALTER TABLE ' || obj_tab.owner || '.' || obj_tab.segment_name || ' ENABLE ROW MOVEMENT';
    shrink_space := 'ALTER TABLE ' || obj_tab.owner || '.' || obj_tab.segment_name || ' SHRINK SPACE CASCADE';
    disable_rowmove := 'ALTER TABLE ' || obj_tab.owner || '.' || obj_tab.segment_name || ' DISABLE ROW MOVEMENT';
  
    EXECUTE IMMEDIATE enable_rowmove;
    EXECUTE IMMEDIATE shrink_space;
    EXECUTE IMMEDIATE disable_rowmove;

    -- このdbms_outputはexecute immediateの前に書くと出力されませんでした

    DBMS_OUTPUT.PUT_LINE(enable_rowmove);
    DBMS_OUTPUT.PUT_LINE(shrink_space);
    DBMS_OUTPUT.PUT_LINE(disable_rowmove);

  END LOOP;

END;
/
spool off

上記スクリプトではサイズの降順で上位20件の表だけ断片化を解消していますが、
断片化の解消が不十分である場合は何度か繰り返し実行することで
表領域の使用サイズがある値に収束していく様子が確認できます。

おわりに

上記では実行時にスキーマや表領域名の入力を促していますが、
例えば表領域名をtoolsdatと小文字で入力すると失敗してしまいます。
改善の余地ありです。