Oracle11gで一時表領域がいっぱいに

あるインスタンスでどうも一時表領域がいっぱいになった模様。autoextendはONになっていますが、サイズがでかすぎてOSの上限に達している。
こんなエラー。

ORA-01652: 一時セグメントを拡張できません

でかすぎるので再作成する。

まず一時表領域の使用率とファイル名を調べる。

ここOracle 一時表領域の使用率を調べる - Break Out!? - blog ver.
の引用です。ありがとうございました。

SELECT dt.file_name, dt.tablespace_name,
to_char(dt.bytes / 1024, '99999990.000') file_kbytes,
to_char(t.bytes_cached / 1024, '99999990.000') used_kbytes,
to_char(t.bytes_cached / dt.bytes * 100, '990.00') || '%' capacity
FROM sys.dba_temp_files dt, v$temp_extent_pool t, v$tempfile v 
WHERE t.file_id(+)= dt.file_id AND dt.file_id = v.file#;

別のファイルを一時表領域に追加

sql> alter tablespace temp add  tempfile '/mnt/db/oracle/oradata/hoge/temp02.dbf'

現行利用している一時表領域のファイルをオフラインにする

sql> alter database tempfile '/mnt/db/oracle/oradata/hoge/temp01.dbf' offline

オフラインにしたデータファイルを削除

sql> alter database tempfile '/mnt/db/oracle/oradata/hoge/temp01.dbf' drop including datafiles ;

データファイルをOracleセッションがつかっている場合はこんなエラーがでます。

ORA-25152: TEMPFILE cannot be dropped at this time

参照しているセッションをalter system killで殺していけばいいのですが、数が多いと面倒。
なので、インスタンスをshtudown,startして一度きれいにするといいでしょう。

削除した一時表領域ファイルを再作成する

sql> alter tablespace temp add '/mnt/db/oracle/oradata/hoge/temp01.dbf' size 1024M autoextend on ;

上記で作成したファイルをオンラインにする

たぶんこの手順は不要。作成した時点でオンラインになってるんじゃないかな。

sql> alter database tempfile '/mnt/db/oracle/oradata/hoge/temp01.dbf' online ;

一時的に作成した一時表領域ファイルをオフラインにして削除

sql> alter database tempfile '/mnt/db/oracle/oradata/hoge/temp02.dbf' offline ;
sql> alter database tempfile '/mnt/db/oracle/oradata/hoge/temp02.dbf' drop including datafiles ;