一時表領域について

質問

システム運用開始前の性能試験で、自動拡張に設定していない一時表領域の容量を超えるOracleのエラー（ORA-1652だったような)が出たため、SQLの修正を行っている最中です。

パフォーマンスチューニングやデータベース容量の設定などには詳しくないため、一時表領域を自動拡張に設定すべきかこのまま自動拡張にしないままにしておくべきかどうかの判断はつかないので、とりあえずそのままにして、一時表領域が容量を超えないようなSQLに修正しようとしています。

ただ、一時表領域のため、容量の見積が出来ず、実際にシステムを動かして確認するしかありません。
自社の環境でもOracleのエラーを再現させることが出来たのですが、一時表領域のサイズが小さくなりません。
これは、エラーのせいなのでしょうか？
また、一時表領域を小さく（0バイト）にしたり、いったん削除して再作成したりは出来ないのでしょうか？

ALTER TABLESPACE、DROP TABLESPACE 共に実行できませんでした。
ちなみに、この一時表領域は、TEMPORARYタイプでユーザのデフォルトの一時表領域になっています。

回答No.2

entree · ベストアンサー

一度大きくなってしまった表領域を小さくすることはできないので、再作成するしかありません。

再作成の方法は、Oracle 9i 以降を使っているのであれば、

CREATE TEMPORARY TABLESPACE temp_new
TEMPFILE 'path' SIZE xxxM;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

DROP TABLESPACE temp_old INCLUDING CONTENTS;

で再作成が可能だったと思います。

お礼

回答ありがとうございます。
再作成が必要なことはわかりましたが、ご紹介いただいた方法では再作成できませんでした。
ALTER TABLESPACEが失敗します。

回答No.3

entree

> ALTER TABLESPACEが失敗します。

よく内容を確認してください。
デフォルト一時表領域の変更は、

ALTER TABLESPACE ......

ではなく、

ALTER DATABASE ......

です。あと、エラーが発生したのであれば、どういうエラーなのかをちゃんと書いていただかないと、アドバイスのしようがありません。

お礼

ALTER DATABASE を実行すると、うまくいきました。
ありがとうございました。

回答No.1

kazuho_goo

一時表領域のエクステントは開放されない=縮小不可です。よって、サイズを小さくするには「新しい一時表領域の作成」→「旧一時表領域の削除」となります。実際の手順は
１．ALTER TABLESPACE TEMP ADD TEMPFILE [新DBファイル名](必要であればAUTOEXTEND OFFも)
２．ALTER DATABASE TEMPFILE [旧DBファイル名] OFFLINE
３．ALTER DATABASE TEMPFILE [旧DBファイル名] DROP　INCLUDING DATAFILES
です。ただし、実際の環境は手元にありませんので確認はしていません。また、あわせて初期化パラメタのSORT_AREA_SIZEの見直しもお勧めします。

お礼

回答ありがとうございます。
ご紹介いただいた方法では再作成できませんでした。
ALTER TABLESPACE ～ OFFLINEが失敗します。