oracle 臨時表空間基本常識和操作

2014.10.14 
一、  臨時表空間作用
     Oracle臨時表空間主要用來做查詢和存放一些緩衝區資料。臨時表空間消耗的主要原因是需要對查詢的中間結果進行排序。重啟資料庫可以釋放臨時表空間，如果不能重啟例項，而一直保持問題sql語句的執行，temp表空間會一直增長。直到耗盡硬碟空間。如果上次磁碟空間消耗達到1GB，那麼臨時表空間就是1GB。也就是說當前臨時表空間檔案的大小是歷史上使用臨時表空間最大的大小。

臨時表空間的主要作用：
  索引create或rebuild；
  Order by 或 group by；
  Distinct 操作；
  Union 或 intersect 或 minus；
  Sort-merge joins；
  analyze。
所以在開發過程中，儘量少出現不必要的排序等操作。

臨時表空間不足會報錯：
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
二、臨時表空間常用操作
   1、檢視臨時表空間 （dba_temp_files檢視）（v_$tempfile檢視）
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys使用者檢視

    2、縮小臨時表空間大小----常用方法，後面會詳細介紹
alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize 100M;

    3、擴充套件臨時表空間：
方法一、增大臨時檔案大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/prod/temp01.dbf’ resize 100m;
方法二、將臨時資料檔案設為自動擴充套件：
SQL> alter database tempfile '/u01/app/oracle/oradata/prod/temp01.dbf’ autoextend on next 5m maxsize unlimited;
方法三、向臨時表空間中新增資料檔案：
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/prod/temp02.dbf’ size 100m;

    4、建立臨時表空間：
SQL> create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/prod/temp11.dbf’ size 10M;

    5、更改系統的預設臨時表空間：
--查詢預設臨時表空間
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改預設臨時表空間
alter database default temporary tablespace temp1;
所有使用者的預設臨時表空間都將切換為新的臨時表空間：
select username,temporary_tablespace,default_ from dba_users;
--更改某一使用者的臨時表空間：
alter user scott temporary tablespace temp;

    6、刪除臨時表空間
刪除臨時表空間的一個資料檔案：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/prod/temp02.dbf’ drop;
刪除臨時表空間(徹底刪除)：
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;
 刪除臨時表空間前，要檢視，所刪除的是否是預設的臨時表空間，如果是，則不能刪除掉。

    7、檢視臨時表空間的使用情況（GV_$TEMP_SPACE_HEADER檢視必須在sys使用者下才能查詢） 
檢視臨時表空間每個資料檔案實際使用量：
set pagesize 50
col tablespace_name for a20
col "Tempfile name" for a42
set linesize 300
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 /1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) -nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)"Used MB",
round((round(nvl(p.bytes_used, 0)/ 1024 /1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as"Used_Rate(%)"
from SYS.V_$TEMP_SPACE_HEADER f,DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) =d.file_id;

    8、查詢消耗資源比較的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
 
    9、檢視當前臨時表空間使用大小與正在佔用臨時表空間的sql語句
select  DISTINCT 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;