小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


Oracle 11g XEでUndo表領域を再作成して縮小する方法


photo credit: naoyafujii via photopin cc

はじめに

Oracle DBに対してinsert/update/deleteを繰り返していると、Undo表領域が肥大化していきます。ディスクの空き容量が枯渇してしまわないよう、Undo表領域を再作成して縮小する方法をまとめてみました。
この記事を書くにあたり、以下の記事を参考にさせていただきました。

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)
松信 嘉範
技術評論社
売り上げランキング: 75,119

UNDO表領域の確認

はじめに、以下のクエリを実行してUNDO表領域の確認を行います。

得られた結果は以下のとおりです。

  • undo_management:AUTOの場合、Oracleがロールバックセグメントを自動管理してくれる
  • undo_retention:UNDOデータの保存期間(デフォルト:900秒)
  • undo_tablespace:UNDO表領域の名前

undo_tablespaceの値からUNDO表領域の名前が「UNDOTBS1」であることが分かります。

表領域のDBFファイルの確認

続いて、以下のクエリを実行して、UNDO表領域の実体ファイルがどこにあるかを確認します。

col tablespace_name for a10
col file_name for a50
col mb for 999999
select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files order by tablespace_name;


この結果からUNDOTBS1の実体ファイルは、C:\oraclexe\app\oracle\oradata\XEにあることが分かります。

UNDO表領域の再作成の流れ

UNDO表領域を再作成するには、以下のような手順を踏みます。

  1. あたらしい表領域(UNDOTBS2)を作成する
  2. UNDO表領域をあたらしい表領域に切り替える
  3. いままでの表領域(UNDOTBS1)を作り直す
  4. UNDO表領域をいままでの表領域に切り替える
  5. あたらしい表領域を削除する
UNDO表領域を再作成する

1.あたらしい表領域(UNDOTBS2)を作成します。

create undo tablespace UNDOTBS2 datafile 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS02.DBF' size 10M;

2.UNDO表領域をあたらしい表領域に切り替えます。

alter system set undo_tablespace = 'UNDOTBS2';

3.いままでの表領域(UNDOTBS1)を作り直します。
いままでの表領域を作り直す前に、自動拡張の状態を調べます。

col file_name for a50
col autoextensible for a3
select file_name,autoextensible from dba_data_files;


UNDOTBS1がYesとなっているので自動拡張がONになっていることが確認できます。以下のクエリを実行し、いままでの表領域を作り直します。

drop tablespace UNDOTBS1;
create undo tablespace UNDOTBS1 datafile 'C:\oraclexe\app\oracle\oradata\XE\UNDOTBS1.DBF' size 100M reuse autoextend on;

このとき、表領域の自動拡張が有効ならautoextend onを指定します。
4.UNDO表領域をいままでの表領域に切り替えます。

alter system set undo_tablespace = 'UNDOTBS1';

5.あたらしい表領域を削除します。

drop tablespace UNDOTBS2;

以上で、Undo表領域を再作成して縮小する手順は完了です。

DB管理者の方は、定期的にUndo表領域が大きくなりすぎていないかをチェックしたほうが良いですね。