oracle

Oracle セグメント縮小とHWMを下げる(ALTER TABLE SHRINK SPACE)

実行手順

alter table xxxxx shrink space cascade とすると表とインデックスのセグメント縮小を行いかつHWMを下げてくれます。注意点としては直前に「行移動の有効化」をする必要があります。DBA_TABLESで確認する事ができますが、デフォルトでは「無効化」されているためです。その後alter table shrink spase cascadeを実行しセグメント縮小とHWMを下げます。最後に忘れず「行移動の無効」に戻しておきましょう。以下が具体的な実行例となります。

-- 行移動の有効化
alter table tbl27 enable row movement
;
-- 表セグメント縮小とHWMを下げる
alter table tbl27 shrink space
;
-- 表と索引セグメントの縮小とHWMを下げる
alter table tbl27 shrink space cascade
;

ここでの表、索引セグメント縮小とはブロック単位で領域を整理する事です。断片化解消と表現されている事もあります。勘違いされやすいところで、索引については「索引再構築(ALTER INDEX REBUILD)」という方法もあります。こちらは索引(インデックス)の再構築をするから結果領域も整理されるイメージです。

-- 忘れず行移動を無効に戻しておきましょう
--
alter table tbl27 disable row movement
;
-- 行移動の有効/無効はDBA_TABLESにて確認する事ができます
--
select OWNER,TABLE_NAME,TABLESPACE_NAME,ROW_MOVEMENT
from dba_tables where TABLE_NAME= 'TBL27' and OWNER ='SOONI'
;
SET LIN 200
COLUMN OWNER FORMAT A10
COLUMN TABLE_NAME FORMAT A10
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN ROW_MOVEMENT FORMAT A12
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,ROW_MOVEMENT
  2  from dba_tables where TABLE_NAME= 'TBL27' and OWNER ='SOONI'
  3  ;

OWNER      TABLE_NAME TABLESPACE ROW_MOVEMENT
---------- ---------- ---------- ------------
SOONI      TBL27      TBS01      ENABLED

SQL> alter table tbl27 shrink space cascade
  2  ;

表が変更されました。

SQL>
SQL> alter table tbl27 disable row movement
  2  ;

表が変更されました。

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,ROW_MOVEMENT
  2  from dba_tables where TABLE_NAME= 'TBL27' and OWNER ='SOONI'
  3  ;

OWNER      TABLE_NAME TABLESPACE ROW_MOVEMENT
---------- ---------- ---------- ------------
SOONI      TBL27      TBS01      DISABLED

SQL>

実際にHWMが高い状況を作成しそれが解消する事を確認してみます


SQL> SET LIN 200
SQL> COLUMN SEGMENT_TYPE FORMAT A20
SQL> COLUMN SEGMENT_NAME FORMAT A20
SQL> COLUMN TABLE_OWNER FORMAT A10
SQL> COLUMN TABLE_NAME FORMAT A10
SQL> COLUMN INDEX_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A20
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN M_BYTE FORMAT 9999999990.9
SQL> COLUMN BLOCKS FORMAT 9999999990

--
-- まずは現状確認
--
SQL> select s.OWNER,s.SEGMENT_NAME,segment_type,s.TABLESPACE_NAME ,s.BYTES / 1024 / 1024 m_byte,s.BLOCKS
  2  from dba_segments s
  3  where
  4  segment_type = 'TABLE'
  5  and segment_name = 'TBL27'
  6  ;

OWNER                SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME        M_BYTE      BLOCKS
-------------------- -------------------- -------------------- --------------- ------------- -----------
SOONI                TBL27                TABLE                TBS01                   352.0       45056

SQL> select i.TABLE_OWNER,i.TABLE_NAME,s.SEGMENT_TYPE,i.INDEX_NAME
  2  ,i.TABLESPACE_NAME,s.BYTES /1024 /1024 m_byte,s.BLOCKS
  3  from dba_segments s inner join dba_indexes i
  4  on s.OWNER = i.OWNER and s.SEGMENT_NAME = i.INDEX_NAME and s.SEGMENT_TYPE = 'INDEX'
  5  and i.TABLE_OWNER ='SOONI' and i.TABLE_NAME ='TBL27'
  6  ;

TABLE_OWNE TABLE_NAME SEGMENT_TYPE         INDEX_NAME      TABLESPACE_NAME        M_BYTE      BLOCKS
---------- ---------- -------------------- --------------- --------------- ------------- -----------
SOONI      TBL27      INDEX                IDX02_TBL27     TBS01                   264.0       33792
SOONI      TBL27      INDEX                UNI_IDX01_TBL27 TBS01                    39.0        4992

-- 大量レコードを削除します。
--
SQL> delete from tbl27 where col1 > 2
  2  ;

1739993行が削除されました。

SQL>
SQL> select s.OWNER,s.SEGMENT_NAME,segment_type,s.TABLESPACE_NAME ,s.BYTES / 1024 / 1024 m_byte,s.BLOCKS
  2  from dba_segments s
  3  where
  4  segment_type = 'TABLE'
  5  and segment_name = 'TBL27'
  6  ;

OWNER                SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME        M_BYTE      BLOCKS
-------------------- -------------------- -------------------- --------------- ------------- -----------
SOONI                TBL27                TABLE                TBS01                   352.0       45056

SQL> select i.TABLE_OWNER,i.TABLE_NAME,s.SEGMENT_TYPE,i.INDEX_NAME
  2  ,i.TABLESPACE_NAME,s.BYTES /1024 /1024 m_byte,s.BLOCKS
  3  from dba_segments s inner join dba_indexes i
  4  on s.OWNER = i.OWNER and s.SEGMENT_NAME = i.INDEX_NAME and s.SEGMENT_TYPE = 'INDEX'
  5  and i.TABLE_OWNER ='SOONI' and i.TABLE_NAME ='TBL27'
  6  ;

TABLE_OWNE TABLE_NAME SEGMENT_TYPE         INDEX_NAME      TABLESPACE_NAME        M_BYTE      BLOCKS
---------- ---------- -------------------- --------------- --------------- ------------- -----------
SOONI      TBL27      INDEX                IDX02_TBL27     TBS01                   264.0       33792
SOONI      TBL27      INDEX                UNI_IDX01_TBL27 TBS01                    39.0        4992

SQL>
-- 
--- 削除後セグメントサイズをみても全く変わっていません。
--- ここでshrinkの登場です
---
SQL> alter table tbl27 shrink space cascade
  2  ;
alter table tbl27 shrink space cascade
*
行1でエラーが発生しました。:
ORA-10636: ROW MOVEMENT is not enabled


SQL>
--- はい。お約束のエラーです。上記述べた通り、事前に行移動の有効化をする必要があります。
SQL> alter table tbl27 enable row movement
  2  ;

表が変更されました。

SQL> alter table tbl27 shrink space cascade
  2  ;

表が変更されました。

SQL>
--- 対象テーブルの状況にもよりますが、少し時間がかかります。しかしながら
--- 別セッションからselectは当然ながらinsert/update/deleteなんでも可能です。
--- オンライン中でもこちら実行可能という事ですね。これはとても助かります

SQL> select s.OWNER,s.SEGMENT_NAME,segment_type,s.TABLESPACE_NAME ,s.BYTES / 1024 / 1024 m_byte,s.BLOCKS
  2  from dba_segments s
  3  where
  4  segment_type = 'TABLE'
  5  and segment_name = 'TBL27'
  6  ;

OWNER                SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME        M_BYTE      BLOCKS
-------------------- -------------------- -------------------- --------------- ------------- -----------
SOONI                TBL27                TABLE                TBS01                     0.1           8

SQL> select i.TABLE_OWNER,i.TABLE_NAME,s.SEGMENT_TYPE,i.INDEX_NAME
  2  ,i.TABLESPACE_NAME,s.BYTES /1024 /1024 m_byte,s.BLOCKS
  3  from dba_segments s inner join dba_indexes i
  4  on s.OWNER = i.OWNER and s.SEGMENT_NAME = i.INDEX_NAME and s.SEGMENT_TYPE = 'INDEX'
  5  and i.TABLE_OWNER ='SOONI' and i.TABLE_NAME ='TBL27'
  6  ;

TABLE_OWNE TABLE_NAME SEGMENT_TYPE         INDEX_NAME      TABLESPACE_NAME        M_BYTE      BLOCKS
---------- ---------- -------------------- --------------- --------------- ------------- -----------
SOONI      TBL27      INDEX                IDX02_TBL27     TBS01                     0.1          16
SOONI      TBL27      INDEX                UNI_IDX01_TBL27 TBS01                     0.1           8

SQL>

-- shrink space cascade が完了した後はこの通りテーブルもインデックスセグメントも小さくなっていること確認できました


スポンサーリンク
コピペで使う