Oracleでデータファイルを縮小する際にORA-03297エラーが出た時の対処

下記のコマンドを実行した際にエラーが出る場合がある。

ALTER DATABASE DATAFILE 'データファイル名' RESIZE サイズ[K|M];
ORA-03297: ファイルには、要求したRESIZE値を超える使用中のデータが含まれています。

これは、データファイルを縮小しようとしたが、使用中のデータがあるために縮小できませんよというエラー。

10GBの表領域で1GBしか使用していないのにエラーが出た

これは、縮小可能なのはデータファイルの一番後ろにあるデータより後ろの部分だけだから。
 
概念としては
■□□□□■□□

■□□□□■
するだけ。
■■
このようにはならない。
 
:使用済みデータブロック
:未使用データブロック

対処方法は主に3つ

1)EXPORT IMPORTコマンド

EXPORTを使用してデータをいったん抜き出し、オブジェクトを削除してからIMPORTコマンドでデータを挿入する。

  1. テーブルスペースAをエクスポートする
  2. テーブルスペースAを削除する
  3. テーブルスペースAを再作成する
  4. インポートする
2)ALTER TABLE 〜 MOVEコマンド
  1. テーブルスペースAの対象データ特定する
  2. テーブルスペースBを作成する
  3. 対象データをテーブルスペースBへ[ALTER TABLE 〜 MOVEコマンド + ALTER INDEX 〜 REBUILDコマンド]を使用して移動する
  4. テーブルスペースAを再作成する(ALTER DATABASE DATAFILE 〜 RESIZE 〜コマンドでも可)
  5. テーブルスペースBからテーブルスペースAへデータを移動(戻す)する

対象データ特定:データファイルの中身(エクステント)について知るにはDBA_EXTENTSを参照する。

SELECT
  'ALTER TABLE ' || E.OWNER || '.' || E.SEGMENT_NAME || ' MOVE TABLESPACE TEST_TS;' AS SQL
FROM
  DBA_EXTENTS E INNER JOIN DBA_DATA_FILES F
    ON E.FILE_ID = F.FILE_ID
WHERE
    FILE_NAME = '・・・/test_ts.dbf'
AND E.SEGMENT_TYPE = 'TABLE'
GROUP BY E.OWNER, E.SEGMENT_NAME

該当テーブルのデータを移動する

ALTER TABLE scott.emp MOVE TABLESPACE users;

 
ALTER TABLE 〜 MOVEでは
ROWID が変更されるため、索引は無効になる
ROWID が変更されるため、索引を再作成する必要がある

ALTER INDEX scott.index01_emp REBUILD;

※ALTER TABLE 〜 MOVEを実行する時は移動先の表領域に権限がないといけない権限追加は下記

ALTER USER SCOTT QUOTA UNLIMITED ON 移動先の表領域名;
3)ALTER TABLE xxx SHRINK SPACE

SHRINKが一番簡単で便利。
SHRINKをするには

  • ローカル管理表領域(EXTENT MANAGEMENT LOCAL 句)かつ
  • 自動セグメント領域管理(SEGMENT SPACE MANAGEMENT AUTO 句) で管理されていること。

縮小を行なう前に行の移動を有効にしておかなければいけない。

ALTER TABLE TABLENAME ENABLE ROW MOVEMENT;

SHRINK は行単位で処理を行なうため行移行・連鎖が発生している場合は効果が低い場合がある
CASCADEをつけることにより、索引・マテリアライズドビュー・LOB索引
IOTマッピング表・オーバーフローセグメントまで同時に縮小してくれる。

<参考URL>