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;