ディスクを圧迫した一時表領域をオンラインのまま縮小する

1年越しにOracleDBで一時表領域の問題にぶち当たり学習したのでメモ。

quotto.hatenablog.com

一時表領域で起こる問題の筆頭と言えば自動拡張を続けディスクを圧迫してしまうことではないでしょうか。

10gまでの復旧方法

10gまでで広く使われている復旧方法は次のとおりです。

1. 一時表領域に新しいデータファイルを追加する。
2. いっぱいになったデータファイルをオフライン化する。
3. いっぱいになったデータファイルを削除する。
4. 再度削除したデータファイルを作成する。
5. 最初に追加したデータファイルをオフライン化する。
6. 追加したデータファイルを削除する。

qiita.com

面倒ですよね。6回もコマンドを実行しなければなりません。

11gからはメチャメチャ便利になった

ところが、11gではなんとコマンド一発でデータファイルが縮小できるんです。

alter tablespace TEMP shrink space keep 1000M;

これだけ。

ポイントはshrink space句で、一時表領域にだけ有効です。shrink space句を指定することで一時表領域をオンラインのまま最適化（デフラグ）して、データファイルのサイズを縮小します。

keep句はオプションで、「最低限ここまで縮小したい！」という時に使います。

詳しくはオンラインドキュメントをどうぞ。

ALTER TABLESPACE

alter database と何が違う？

データファイルを圧縮するだけであれば、alter databaseでも実現できるかもしれません。

alter databse tempfile '/datafaile01/temp01.dbf' resize 1000M

しかしこれが実行可能な条件は、「使用中領域」＋「再利用可能領域」が1000Mを超えていないことです。

つまり未使用領域が残っていないとダメ。冒頭で挙げたディスクを圧迫する状態というのは、一時表領域のほぼ全域を「再利用可能領域」で埋めてしまっているため、大抵のケースではこの方法でデータフィルを縮小することはできないと思われます。

それに対してalter tablespace shrink spaceは「再利用可能領域」＋「未使用領域」に対して縮小をかけます。
つまり再利用可能領域を未使用領域にして、ディスクブロックを空けていくんです。これにより使用中領域以上のサイズまで縮小が可能です。

また10gまでで行っていた一時表領域のオフライン化は使用中領域が無いことが前提となるため、こちらの方が使い勝手が良いと思います。
一時表領域を使う検索が行われている状態でもデータファイルを縮小することができます。

一時表領域の内訳

これまた11gから一時表領域の内訳が確認できるdba_temp_free_spaceテーブルが用意されています。

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME  TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
---------------- --------------- --------------- ----------
TEMPTS1          20971520        12582912        19922944

• TABLESPACE_SIZE：一時表領域のサイズ
• ALLOCATED_SPACE：使用中領域＋再利用可能領域
• FREE_SPACE：再利用可能領域＋未使用領域（shrink spaceで圧縮可能なサイズ）

DBA_TEMP_FREE_SPACE

絵で見てわかるOracleの仕組み (DB Magazine SELECTION)