ORACLE 暫存資料表空間使用率過高的原因及臨時解決方案

 

        資料庫temp暫存資料表空間增大，一般在資料安裝時設定暫存資料表空間大小，預設的情況下都是設定為自動成長。這樣會引起一個問題：在資料庫使用中temp資料表空間會自動擴充的越來越大，造成磁碟空間使用不足。

引起暫存資料表空間增大主要使用在以下幾種情況：
1、order by or group by (disc sort佔主要部分)；
2、索引的建立和重建立；
3、distinct操作；
4、union & intersect & minus sort-merge joins；

5、Analyze 操作；
6、有些異常也會引起TEMP的暴漲。

 

解決temp暫存資料表空間大小問題：

一.直接縮小temp資料表空間大小

alter database tempfile 'd:powerdmsdb\dmsdb\temp01.dbf' resize 1024M;

此語句會直接修改temp資料表空間的大小，但可能會執行不成功，因為當temp使用率為100%或當前有會話佔用時，temp資料表空間是無法縮小的。

查詢當前會話：

SELECT se.username, 
       se.sid, 
       se.serial#, 
       se.sql_address, 
       se.machine, 
       se.program, 
       su.tablespace, 
       su.SEGTYPE, 
       su.CONTENTS
  FROM v$session se, 
       v$sort_usage su 
 WHERE se.saddr=su.session_addr 

利用此sql語句查詢當前會話，然後kill當前會話：

Alter system kill session 'sid,serial#'

執行此語句後再對temp資料表空間resize空間大小就可以了。

註：此方法只是對temp資料表空間做臨時性的縮小，以後還會繼續增大。 

方法二：

對temp暫存資料表空間重建並設定增長上限值，設定這個值時要預先估算設定合理，不然當增大到此值時會出錯，在合理的範圍內增加到上限值時資料庫會自動釋放temp暫存資料表空間。

重建temp暫存資料表空間：

1.--啟動資料庫

startup 

2.--建立中轉暫存資料表空間
create TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\PowerDmsDB\DMSDB\TEMP02.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M; 
3.--改變預設暫存資料表空間 為剛剛建立的新暫存資料表空間temp2
alter database default temporary tablespace temp2;   
4.--刪除原來暫存資料表空間
drop tablespace temp including contents and datafiles;

5.--重新建立暫存資料表空間
create TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\PowerDmsDB\DMSDB\TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;
                                  
6.--重設預設暫存資料表空間為建立的temp資料表空間
alter database default temporary tablespace temp; 

7.--刪除中轉用暫存資料表空間
drop tablespace temp2 including contents and datafiles;

 8.--重新指定使用者資料表空間為重建的暫存資料表空間
alter user zxd temporary tablespace temp; 

至此暫存資料表空間增長過大可以更改完成。 

下面是查詢在sort排序區使用的執行耗時的SQL:
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as    Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr
order by se.username,se.sid

此語句可以做跟蹤查看分析時用。