oracle的臨時表空間

oracle資料庫的臨時表空間的主要用途是在資料庫進行排序運算，管理索引，訪問檢視等操作時提供臨時的運算空間，當運算完成之後系統會自動清理。當oracle資料庫要用到排序操作sort，pga中sort_area_size大小不夠時，將會把這些資料放入臨時表空間進行排序同時如果有異常情況也會被放入臨時表空間。正常來說，在完成select語句，creat index等一些使用temp表空間的排序操作之後，oracle會自動釋放臨時段的，這裡說的釋放，僅僅是將這些空間標記為空閒，並可重用，真正佔用的磁碟空間並沒有釋放，所以temp表空間可能會越來越大。
透過重啟資料庫可以釋放臨時表空間，如果不能重啟例項，而一直保持有問題的sql語句執行，temp表空間會一直增長，知道消耗完硬碟空間為止。
在臨時表空間的磁碟分配上，oracle採用的是貪心演算法，如果上次磁碟空間消耗達到1GB,那麼臨時表空間就是1GB，也就是說當前臨時表空間的大小是歷史上使用臨時表空間的最大值。
臨時表空間主要用於：
(1)create index或rebuild index
(2)order by 或group by
(3)distinct操作
(4)union或intersect或minus
(5)sort-merge joins
(6)analyze
排序時很耗資源的，不能無限增大temp表空間，關鍵是最佳化你的語句，儘量使排序減少才是上策。

檢視當前資料庫臨時表空間和臨時資料檔案使用率：
SYS@orcl 07-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
  2  total-used as "free|MB",
  3  total as "total|MB"
  4  from 
  5  (select tablespace_name,sum(bytes_used)/1024/1024 used
  6  from gv_$temp_space_header
  7  group by tablespace_name) temp_used,
  8  (select file_name,tablespace_name,sum(bytes)/1024/1024 total
  9  from dba_temp_files
 10  group by tablespace_name,file_name) temp_total
 11  where temp_used.tablespace_name=temp_total.tablespace_name;


TABLESPACE_NAME FILE_NAME                              free|MB   total|MB
--------------- ----------------------------------- ---------- ----------
TEMP            /u01/app/oracle/oradata/orcl/temp01         12         20
                .dbf


檢視當前資料庫預設的臨時表空間：
SYS@orcl 07-SEP-14>select property_name,property_value
  2  from database_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                       PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP


改變臨時表空間的大小：
SYS@orcl 07-SEP-14>alter database tempfile
  2  '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 25m;


Database altered.

SYS@orcl 07-SEP-14>select tablespace_name,file_name,bytes/1024/1024||'MB'
  2  from dba_temp_files;


TABLESPACE_NAME FILE_NAME                                          BYTES/1024/1024||'MB'
--------------- -------------------------------------------------- ------------------------------------------
TEMP            /u01/app/oracle/oradata/orcl/temp01.dbf            25MB


將臨時表空間設定成自動擴充套件：
SYS@orcl 07-SEP-14>alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;

Database altered.

向臨時表空間增加資料檔案:
SYS@orcl 07-SEP-14>alter tablespace temp 
  2  add tempfile '/u01/app/oracle/oradata/orcl/temp01a.dbf' size 10m;

Tablespace altered.

YS@orcl 07-SEP-14>select tablespace_name,file_name from dba_temp_files;


TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
TEMP            /u01/app/oracle/oradata/orcl/temp01a.dbf
TEMP            /u01/app/oracle/oradata/orcl/temp01.dbf


檢視臨時檔案的大小，自動擴充套件性：
SYS@orcl 07-SEP-14>select file_name,tablespace_name,bytes/1024/1024||'MB',
  2  autoextensible from dba_temp_files;


FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ---------------
BYTES/1024/1024||'MB'                      AUT
------------------------------------------ ---
/u01/app/oracle/oradata/orcl/temp01a.dbf           TEMP
10MB                                       NO


/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP
25MB                                       YES


oracle資料庫不能drop預設的臨時表空間
SYS@orcl 07-SEP-14>drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

建立新的臨時表空間
SYS@orcl 07-SEP-14>create temporary tablespace temp2 
  2  tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 20m
  3  reuse autoextend on next 1m maxsize unlimited;

Tablespace created.

改變預設的臨時表空間為temp2
SYS@orcl 07-SEP-14>alter database default temporary tablespace temp2;

Database altered.

檢視預設的臨時表空間：
SYS@orcl 07-SEP-14>select property_name,property_value 
  2  from database_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE';


PROPERTY_NAME                       PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP2

SYS@orcl 07-SEP-14>drop tablespace temp;

Tablespace dropped.

SYS@orcl 07-SEP-14>select file_name,tablespace_name from dba_temp_files;


FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/orcl/temp02.dbf            TEMP2


在oracle資料庫中，我們可以建立多個臨時表空間，並把它們組成一個臨時表空間組，這樣應用資料用於排序時可以使用組裡的多個臨時表空間，一個臨時表空間組至少有一個臨時表空間，其最大個數是沒有限制的。注意，組的名字不能和其中某個表空間的名字相同。
臨時表空間組是在建立臨時表空間時透過指定group字句建立的，如果刪除組中所有的臨時表空間，那麼這個組也將會消失。
我們可以把一個表空間從 一個組轉移到另一個組，也可以從一個組中刪除臨時表空間或者新增臨時表空間。
使用臨時表空間組有如下優點：
1.可以同時指定多個臨時表空間，避免由於臨時表空間不足造成的磁碟排序問題。
2.當一個使用者同時有多個會話時，可以使得它們使用不同的臨時表空間。
3.並行操作中，不同的從屬程式可以使用不同的臨時表空間。

SYS@orcl 07-SEP-14>create temporary tablespace tempts1 tempfile
  2  '/u01/app/oracle/oradata/orcl/tempts01.dbf' size 5m tablespace group group1;

Tablespace created.

SYS@orcl 07-SEP-14>create temporary tablespace tempts2 tempfile
  2  '/u01/app/oracle/oradata/orcl/tempts02.dbf' size 5m tablespace group group2;

Tablespace created.

檢視臨時表空間組dba_tablespace_groups檢視：
SYS@orcl 07-SEP-14>select * from dba_tablespace_groups;


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


在臨時表空間中進行shrink(oracle11g新功能)

查詢當前臨時表空間和臨時資料檔案：
SYS@orcl 08-SEP-14>col tablespace_name for a15
SYS@orcl 08-SEP-14>col file_name for a35
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
  2  total-used as "free|MB",
  3  total as "total|MB"
  4  from 
  5  (select tablespace_name,sum(bytes_used)/1024/1024 used
  6  from gv_$temp_space_header
  7  group by tablespace_name) temp_used,
  8  (select file_name,tablespace_name,sum(bytes)/1024/1024 total
  9  from dba_temp_files
 10  group by tablespace_name,file_name) temp_total
 11  where temp_used.tablespace_name=temp_total.tablespace_name;


TABLESPACE_NAME FILE_NAME                              free|MB   total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2         /u01/app/oracle/oradata/orcl/tempts          4          5
                02.dbf


TEMPTS1         /u01/app/oracle/oradata/orcl/tempts          4          5
                01.dbf


TEMP2           /u01/app/oracle/oradata/orcl/temp02         18         20
                .dbf


將temp2表空間收縮為15m：
SYS@orcl 08-SEP-14>alter tablespace temp2 shrink space keep 15m;

Tablespace altered.
再次檢視臨時表空間和臨時資料檔案：
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
  2  total-used as "free|MB",
  3  total as "total|MB"
  4  from 
  5  (select tablespace_name,sum(bytes_used)/1024/1024 used
  6  from gv_$temp_space_header
  7  group by tablespace_name) temp_used,
  8  (select file_name,tablespace_name,sum(bytes)/1024/1024 total
  9  from dba_temp_files
 10  group by tablespace_name,file_name) temp_total
 11  where temp_used.tablespace_name=temp_total.tablespace_name;


TABLESPACE_NAME FILE_NAME                              free|MB   total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2         /u01/app/oracle/oradata/orcl/tempts          4          5
                02.dbf


TEMPTS1         /u01/app/oracle/oradata/orcl/tempts          4          5
                01.dbf


TEMP2           /u01/app/oracle/oradata/orcl/temp02         15         16
                .dbf
temp2的資料檔案縮小為15m。




[oracle@localhost orcl]$ ll
total 156872
-rw-r----- 1 oracle oinstall 52429312 Sep  8 00:44 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep  8 00:44 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep  8 01:02 redo03.log
-rw-r----- 1 oracle oinstall 16785408 Sep  8 00:55 temp02.dbf
-rw-r----- 1 oracle oinstall  5251072 Sep  7 21:27 tempts01.dbf
-rw-r----- 1 oracle oinstall  5251072 Sep  7 21:28 tempts02.dbf


oracle的臨時表不佔用任何表空間，而且不同的session之間互相看不到對方的資料。
在會話結束後表中的資料會自動清空，如果選了delete rows，則在提交的時候即清空資料，preserve則一直到會話結束。

commit後保留臨時資料：
SYS@orcl 08-SEP-14>create global temporary table tablename(
  2  col1 varchar2(10),
  3  col2 number
  4  )on commit preserve rows;

Table created.

commit後不保留臨時資料：
SYS@orcl 08-SEP-14>create global temporary table tablename1(
  2  col1 varchar2(10),
  3  col2 number
  4  )on commit delete rows;

Table created.