Oracleの一時表領域を縮めるコツ

これまた火消しネタだけど、「へぇー」という意見が多かったのでまとめておく。
まず、目的は『TEMP表領域(一時表領域)を意味もなく大きく確保してしまったので削除したい』でした。で、担当者がALTER TABLESPACE でサイズ削減にチャレンジしたところ、使用中領域があって失敗、と出てhelp要請が。

ALTER TABLESPACE COALESCEについて

担当者もそこはカンをはたらかせて、きっとエクステントの分断が起こっているに違いない、というわけで、ケツのほうの領域を前に圧縮してやればいい(defragですな)と思い立ってALTER TABLESPACE .. COALESCEを実行。その後リサイズにチャレンジするも撃沈。
で、COALESCEなんですけど、これは別にデフラグツールではないのです。DOS時代のメモリ管理にもよくあったことだけど「空き領域もブロック管理されていて、たとえば 100MBの空き領域と50MBの空き領域が連続しているときに、この空き領域を結合して、150MBのものにするよ」というようなことです。要するに現在確保済みのEXTENTを動かすようなことはしてくれないはずです(はず、としか言えないのが悲しいが)。よって、これは勘違い、ダメです。

一時表領域のクセについて

一時表領域ってそもそもなんなんでしょうね?　これはまず、用語本来の意味として、ユーザーがソートなどの操作時に使用する一時的な表領域、となっています。そして、これは別にどの表領域でもいいです。デフォルトはこともあろうにSYSTEM表領域だというぐらいに(最悪だと思うのだが、そうでもないのか? > Oracle)。
で、もう一つ別のレイヤー?の用語として「TEMPORARY属性な表領域」というのがあります。専用一時表領域とか言われてて、Oracle7.3からサポートされたとのこと。また、ややこしいことにOracle8i以降は「テンポラリファイルを使った専用一時表領域」なんていうものまで存在しますが、とりあえずこれは置いておきます。
で、その専用一時表領域のクセですが、

確保されたセグメントは削除されない
エクステントの管理領域はSGA内

というのがあります。どちらも、いかにもパフォーマンス向上に効きそうですね。でも、この前者がやっかいなのです。
ある担当者がちょっと間違ってでかいORDER BYを走らせた、とかHASH JOINで3表結合やっちゃった、とかそんなことでソートがガンガン走りTEMP表領域にエクステントが生成され…というのはよくある話だと。で、これで確保されたセグメントってどうやら解放されないらしいのです。ガベコレなし、というようなことなのかな?

で、どうするか

なんか、私も確信はなかったのですが、

> ALTER TABLESPACE TEMP PERMANENT;
しばらく放置
> ALTER TABLESPACE TEMP TEMPORARY;

という感じで、一時的に「専用一時表領域ではなくする」ことで、ガベコレというか、未使用のセグメントの解放が実施され(ちょっとこのへん謎だが)、ケツのほうにあったゴミエクステントはなくなった模様(作戦成功)。で、これでめでたくRESIZEできるようになったわけです。
真のOraclerにとってはこんな方法ではなくもっとスマートな方法があるのかもしれない。

Oracleって、やっぱなかなか面白いですね。