実行手順
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 が完了した後はこの通りテーブルもインデックスセグメントも小さくなっていること確認できました
