Oracle中暫時資料表空間的清理

標籤：temporary   log   rac   pac   contents   session   刪除   查詢   post   

iamlaosong

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;

Oracle中暫時資料表空間的清理