Oracle中臨時表空間的清理

Oracle中臨時表空間的清理

Oracle臨時表空間主要用來做查詢和存放一些緩沖區數據。臨時表空間消耗的主要原因是需要對查詢的中間結果進行排序。臨時表空間的主要作用：

 

索引create或rebuild
Order by 或 group by
Distinct 操作
Union 或 intersect 或 minus
Sort-merge joins
analyze

重啟數據庫可以釋放臨時表空間，如果不能重啟實例，而一直保持問題sql語句的執行，temp表空間會一直增長。即使重建了臨時表空間，過一段時間後，臨時表空間的使用率就達到99%，然後，表空間就開始增長，直到耗盡硬盤空間。如下圖所示臨時表空間，剛建立時是8G，現在已到32G：

為了防止臨時表空間無限制的增加，我采用隔一段時間就重建臨時表空間的方法，為了方便，我保留兩組語句，輪流執行即可，假定現在臨時表空間名稱是temp，新建一個tempa表空間，刪除temp表空間，方法如下：

create temporary tablespace TEMPA TEMPFILE '/opt/app/oracle/oradata/orcl/tempa01.dbf ' SIZE 8192M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED; --創建中轉臨時表空間
alter database default temporary tablespace tempa; --改變缺省臨時表空間

drop tablespace temp including contents and datafiles; --刪除原來臨時表空間

新建的臨時表空間如下圖所示：

過一段時間，當臨時表空間增長到一定的程度，再新建一個temp表空間，刪除tempa表空間，即：

create temporary tablespace TEMP TEMPFILE '/opt/app/oracle/oradata/orcl/temp01.dbf ' SIZE 8192M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED; --創建中轉臨時表空間
alter database default temporary tablespace temp; --改變缺省臨時表空間
drop tablespace tempa including contents and datafiles; --刪除原來臨時表空間

這樣就可以保證臨時表空間不至於過大，防止過多的占用有限的硬盤空間。

=====================================================

用下面語句可查看當前臨時表空間使用空間大小與正在占用臨時表空間的sql語句：

select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;

下面語句查詢臨時表空間的空閒程度：

select 'the ' || name || ' temp tablespaces ' || tablespace_name ||
' idle ' ||
round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
'% at ' || to_char(sysdate, 'yyyymmddhh24miss')
from (select d.tablespace_name tablespace_name,
nvl(sum(used_blocks), 0) tot_used_blocks,
sum(blocks) total_blocks
from v$sort_segment v, dba_temp_files d
where d.tablespace_name = v.tablespace_name(+)
group by d.tablespace_name) s,
v$database;