oracle 暫存資料表空間使用方式

標籤：tin   session   建立   join   查詢   思維   desc   acl   alter   

今天使用者那邊執行一個很簡單的SQL，輸出結果集也才幾萬條，涉及三表，最大也才100萬資料量，結果卻報了資料表空間不足的情況，理論來說，這樣的SQL怎麼也不應該吃這麼多暫存資料表空間。

查詢暫存資料表空間的使用方式：

select USERNAME,SESSION_NUM,SQL_ID,TABLESPACE,SEGTYPE,BLOCKS*8192/1024/1024||‘ MB‘ as MB from v$sort_usage order by 6 desc

v$sort_usage顯示給定執行個體的每個排序段的資訊，只有發生在暫存資料表空間的操作才會更新該視圖。這裡不僅僅記載排序動作，只要在暫存資料表空間操作就會記錄，而且排序發生在記憶體，也不會更新該視圖的。

一般固定思維我都會認為此SQL有問題，然後我執行了一次之後，立馬就拋異常了，我此時即時反應過來不對勁，因為SQL如果要暫用暫存資料表空間很多，也是逐步一點一點索取空間佔用的，這異常拋出太快了，轉而我就去查詢了dba_temp_files,果然是因為設計的問題，暫存資料表空間建立的不合理，真的是空間太小的緣故：

alter database tempfile xxx autoextend on next 200m maxsize unlimited;

暫存資料表空間佔用的幾大情境：

1、order by or group by ；

2、索引的建立和重建立；

3、distinct、SORT-MERGE JOINS、HASH JOIN等操作；

4、union & intersect & minus sort-merge joins；

5、其他異常操作

oracle 暫存資料表空間使用方式