前回までで表領域を断片化させたので、今回は断片化の解消方法を確認してみます。

SQL> alter tablespace BIG_TBS resize 350m ;
alter tablespace BIG_TBS resize 350m
*
行1でエラーが発生しました。:
ORA-03297:
ファイルには、リクエストしたRESIZE値を超える使用中のデータが含まれています。

例えば、ディスクに空き領域が無い状態で200MBのサイズの領域が必要になったとします。
その場合、既存の表領域を550MB→350MBに減らします。
しかし、領域が断片化しているため縮小できません。

set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
       T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
  from DBA_EXTENTS T1
 where ((T1.BLOCK_ID + 1)
        * (select BLOCK_SIZE
             from DBA_TABLESPACES T2
            where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
   and TABLESPACE_NAME = 'BIG_TBS'
 order by 1, 2;

sizeに値を入力してください: 350
旧   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
新   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (350 * 1024 * 1024)

TABLESPACE_NAME                   FILE_ID SEGMENT_TYPE       SEGMENT_NAME             SEGMENT_TYPE
------------------------------ ---------- ------------------ ------------------------ ------------------
BIG_TBS                                 7 INDEX              TEST_USER1.PK_TAB1_COL1  INDEX
BIG_TBS                                 7 INDEX              TEST_USER1.PK_TAB3_COL1  INDEX
BIG_TBS                                 7 INDEX              TEST_USER1.PK_TAB4_COL1  INDEX
BIG_TBS                                 7 TABLE              TEST_USER1.TAB4          TABLE

350MB以降に存在しているセグメントを確認します。
主キー索引とテーブルが存在しているようです。
これを350MBより前に移動することで550MB→350MBの縮小ができるようになります。

alter table TAB4 move ;
alter index PK_TAB4_COL1 rebuild online ;
alter index PK_TAB1_COL1 rebuild online ;
alter index PK_TAB3_COL1 rebuild online ;

表はalter table ～ move、索引はalter index ～ rebuildで領域が最適化されます。

set linesize 150 pages 5000
col SEGMENT_NAME for a24
select distinct T1.TABLESPACE_NAME, T1.FILE_ID,
       T1.SEGMENT_TYPE, T1.OWNER||'.'||T1.SEGMENT_NAME "SEGMENT_NAME", T1.SEGMENT_TYPE
  from DBA_EXTENTS T1
 where ((T1.BLOCK_ID + 1)
        * (select BLOCK_SIZE
             from DBA_TABLESPACES T2
            where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
   and TABLESPACE_NAME = 'TBS17_FOR_TAB'
 order by 1, 2;

sizeに値を入力してください: 350
旧   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (&SIZE * 1024 * 1024)
新   7:             where T1.TABLESPACE_NAME = T2.TABLESPACE_NAME)) + BYTES > (350 * 1024 * 1024)

レコードが選択されませんでした。

実行後、再度確認すると350MB以降にセグメントが無くなりました。

SQL> alter tablespace BIG_TBS resize 350m ;

表領域が変更されました。

これで断片化が解消し、550MB→350MBの縮小が出来るようになりました。