ORACLE 臨時表空間滿了的原因解決方案

臨時表空間作用 
Oracle臨時表空間主要用來做查詢和存放一些緩衝區資料。臨時表空間消耗的主要原因是需要對查詢的中間結果進行排序。 
重啟資料庫可以釋放臨時表空間，如果不能重啟例項，而一直保持問題sql語句的執行，temp表空間會一直增長。直到耗盡硬碟空間。
資料庫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 '/u01/app/oracle/oradata/orcl/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 '/u01/app/oracle/oradata/orcl/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 '/u01/app/oracle/oradata/orcl/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

　　此語句可以做跟蹤檢視分析時用。

擴充套件臨時表空間： 
方法一、增大臨時檔案大小： 
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m; 
方法二、將臨時資料檔案設為自動擴充套件： 
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited; 
方法三、向臨時表空間中新增資料檔案： 
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m; 
臨時表空間組介紹 
  1）建立臨時表空間組： 
create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2M tablespace group group1; 
create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2M tablespace group group2; 
  
 2）查詢臨時表空間組：dba_tablespace_groups檢視 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP1                         TEMPTS1 
GROUP2                         TEMPTS2 

 3）將表空間從一個臨時表空間組移動到另外一個臨時表空間組： 
alter tablespace tempts1 tablespace group GROUP2 ; 
select * from dba_tablespace_groups; 

GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP2                         TEMPTS1 
GROUP2                         TEMPTS2 

 4）把臨時表空間組指定給使用者 
alter user scott temporary tablespace GROUP2; 

 5）在資料庫級設定臨時表空間 
alter database <db_name> default temporary tablespace GROUP2; 

 6）刪除臨時表空間組 (刪除組成臨時表空間組的所有臨時表空間) 
drop tablespace tempts1 including contents and datafiles; 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 
------------------------------ ------------------------------ 
GROUP2                         TEMPTS2 

drop tablespace tempts2 including contents and datafiles; 
select * from dba_tablespace_groups; 
GROUP_NAME                     TABLESPACE_NAME 

對臨時表空間進行shrink（11g新增的功能） 
--將temp表空間收縮為20M 
alter tablespace temp shrink space keep 20M; 
--自動將表空間的臨時檔案縮小到最小可能的大小 
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;