臨時表空間是Oracle體系結構中比較特殊的結構。通常情境下，資料庫使用者只需要設定對應的臨時表空間（到使用者），臨時段分配等工作都是系統自動完成。當臨時資料不需要時，Oracle後臺程式SMON也會負責將臨時段回收。

在Oracle的備份恢復體系中，臨時檔案的地位比較低。在進行備份動作時，RMAN都不會進行臨時檔案恢復。在恢復啟動過程中，如果發現臨時檔案不存在，通常Oracle也會自動將臨時檔案建立出來。

1、Temp漫談

Oracle臨時表空間主要充當兩個主要作用：臨時表資料段分配和排序彙總溢位段。我們建立的臨時表，在使用過程中，會有大量的資料段結構的分配。這個分配就是利用臨時表空間。

排序彙總溢位的範圍比較廣泛。我們在SQL語句中進行order by/group by等操作，首先是選擇PGA的記憶體sort area、hash area和bitmap area。如果SQL使用排序空間很高，單個server process對應的PGA不足以支撐排序要求的時候，臨時表空間會充當排序段的資料寫入。這樣排序動作會從記憶體過程退化為外儲存過程。

兩個現象：如果我們的Temp表空間檔案設定比較小，並且設定為不可自動擴充。同時我們又希望給一個很大資料表加索引，經常會遇到：create index語句長時間執行之後報錯，說Temp表空間不能擴充，操作被停止。索引葉子節點是有序的，建立索引的過程也就伴隨著資料庫的排序動作。

另一個現象：如果我們的記憶體設定不合理，SQL經常包括很多“無意義”的“大排序”。這樣會發現我們的Temp空間消耗比較大，一些SQL效能抖動比較明顯。

合理的設定Temp空間管理策略，是應用系統架構的一個重要環節。

2、給臨時表指定表空間

Oracle中，使用者schema和表空間儲存結構對應關係是很靈活的。如果使用者有空間配額（Quota），我們是可以在schema中建立任何表空間的資料表的，是可以把物件放置在任何的表空間裡面。

但是對於11g之前，Temp表空間並不是這樣的。我們建立使用者之後，需要制定出這個使用者schema對應的臨時表空間。如果我們不指定，Oracle會選擇系統預設臨時表空間（通常是temp）作為這個使用者的臨時表空間。

至此以後，這個使用者所有的臨時段都是在這個臨時表空間上進行分配。我們是沒有能力指定某個臨時表分配在其他臨時表空間裡面的。

我們到11g之後，Oracle提供了這樣的自由。

SQL> select * from v$version;

BANNER

-----------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

當前sys使用者的預設表空間為TEMP。

SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SYS';

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYSTEM TEMP

此時，資料庫中包括兩個臨時表空間。

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

--------------- --------------- --------------- ----------

TEMP 481296384 481296384 480247808

TEMPTEST 5368709120 1048576 5367660544

我們可以建立出一個不屬於TEMP預設臨時表空間的臨時表。

SQL> create global temporary table t_temp tablespace temptest as select * from t where 1=0;

Table created

此後的臨時段分配，都是在temptest表空間上進行的。

SQL> insert into t_temp select * from t;

19360512 rows inserted

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

--------------- --------------- --------------- ----------

TEMP 481296384 481296384 480247808

TEMPTEST 5368709120 2248146944 3120562176

那麼，是不是和資料表一樣，支援move操作呢？

SQL> create global temporary table t_temp tablespace temp as select * from dba_objects where 1=0;

create global temporary table t_temp tablespace temp as select * from dba_objects where 1=0

ORA-14451: unsupported feature with temporary table

看來，目前版本還沒有支援move操作的臨時表。

3、臨時表空間、檔案的shrink

臨時表空間是不會有持久化資料儲存的。所以，很多被“脹大”的表空間都存在一個shrink收縮問題。從11g開始，Oracle支援Temp表空間和臨時檔案的搜尋方法。

為了進行試驗，我們先向使用表空間Temptest新增檔案。

SQL> alter tablespace temptest add tempfile size 1G;

Tablespace altered

SQL> select file_name, file_id, tablespace_name from dba_temp_files;

FILE_NAME FILE_ID TABLESPACE_NAME

------------------------------ ---------- ---------------

/u01/app/oradata/ORA11G/datafi 1 TEMP

le/o1_mf_temp_92t73qm8_.tmp

/u01/app/oradata/ORA11G/datafi 2 TEMPTEST

le/o1_mf_temptest_9j80859z_.tm

p

/u01/app/oradata/ORA11G/datafi 3 TEMPTEST

le/o1_mf_temptest_9j826c9b_.tm

p

空間情況：

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

--------------- --------------- --------------- ----------

TEMP 481296384 481296384 480247808

TEMPTEST 6442450944 2249195520 6440353792

新加入臨時檔案到臨時表空間，由於檔案採用稀疏檔案結構，所以我們allocated_space沒有增加，而free_space有增加。

磁碟空間也不會變化。

[root@SimpleLinux ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 48G 26G 20G 57% /

tmpfs 6.0G 256M 5.8G 5% /dev/shm

/dev/mapper/VolGrp01-lv1

194M 5.6M 179M 4% /voltest01

[root@SimpleLinux ORA11G]# cd datafile/

[root@SimpleLinux datafile]# ls -l | grep temptest

-rw-r----- 1 oracle oinstall 5368717312 Feb 19 09:10 o1_mf_temptest_9j80859z_.tmp

-rw-r----- 1 oracle oinstall 1073750016 Feb 19 09:28 o1_mf_temptest_9j826c9b_.tmp

我們可以直接使用shrink tempfile的方法，將檔案限制大小。Keep字句中包括控制大小。

SQL> alter tablespace temptest shrink tempfile '/u01/app/oradata/ORA11G/datafile/o1_mf_temptest_9j826c9b_.tmp' keep 500m;

檔案系統中，檔案顯示出的大小便為500M，但是磁碟分配沒有變化，因為從來就沒有分配過。

[root@SimpleLinux datafile]# ls -l | grep temptest

-rw-r----- 1 oracle oinstall 5368717312 Feb 19 09:10 o1_mf_temptest_9j80859z_.tmp

-rw-r----- 1 oracle oinstall 525336576 Feb 19 09:35 o1_mf_temptest_9j826c9b_.tmp

[root@SimpleLinux datafile]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 48G 26G 20G 57% /

tmpfs 6.0G 256M 5.8G 5% /dev/shm

/dev/mapper/VolGrp01-lv1

194M 5.6M 179M 4% /voltest01

縮小的500M，在dba_temp_free_space中有所表現。

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

--------------- --------------- --------------- ----------

TEMP 481296384 481296384 480247808

TEMPTEST 5894037504 2249187328 5891948544

如果我們對那個已經分配的臨時檔案進行shrink，是會影響到磁碟結構的。

SQL> alter tablespace temptest shrink tempfile '/u01/app/oradata/ORA11G/datafile/o1_mf_temptest_9j80859z_.tmp' keep 1G;

Tablespace altered (長時間執行)

[root@SimpleLinux datafile]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda1 48G 25G 21G 55% /

tmpfs 6.0G 256M 5.8G 5% /dev/shm

/dev/mapper/VolGrp01-lv1

194M 5.6M 179M 4% /voltest01

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

--------------- --------------- --------------- ----------

TEMP 481296384 481296384 480247808

TEMPTEST 1600110592 2080768 1598029824

此外，我們也是可以對Temp表空間直接進行shrink過程。

SQL> create temporary tablespace temptest tempfile size 1G

2 extent management local uniform size 1m;

Tablespace created

SQL> alter tablespace temptest shrink space keep 500m;

Tablespace altered

此時檢查檢視dba_temp_free_space。

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

------------------------------ --------------- --------------- ----------

TEMP 30408704 30408704 29360128

TEMPTEST 525336576 1048576 524288000

影響到的就是表空間總大小。如果我們不指定keep，Oracle會將表空間縮小到後設資料階段。

SQL> alter tablespace temptest shrink space;

Tablespace altered

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

------------------------------ --------------- --------------- ----------

TEMP 30408704 30408704 29360128

TEMPTEST 2088960 1040384 1048576

確定了2M大小，1M是分配後設資料。

4、結論

11g中提供了很多臨時表空間操作的特性，這幫助我們更好的管理和控制臨時表空間。

Oracle 11g中Temp臨時表空間、檔案的新特性

相關文章