臨時表相關

臨時表在日常工作中可能使用比較多，但是大家都對臨時表相關的一些知識了解比較少。我們來簡單梳理一下。

首先是臨時表空間，臨時表都存儲在臨時表空間中，對于臨時表空間，從資料庫中查詢資料字典就能夠很清楚的看到，臨時表空間是nologging的，也就是臨時表也是nologging的。

SQL> select tablespace_name,logging from dba_tablespaces;

TABLESPACE_NAME                LOGGING

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

SYSTEM                         LOGGING

SYSAUX                         LOGGING

UNDOTBS                        LOGGING

TEMPTS1                        NOLOGGING

TEST_DATA1                     LOGGING

POOL_DATA                      LOGGING

TEST_DELETE                    LOGGING

TEST                           LOGGING

8 rows selected.

其次是臨時檔案，臨時表空間的實體存儲就是臨時檔案，從實體層面來說，還是有一些玄機。臨時檔案感覺和11g的一個特性deferred_segment_creation有些像。

首先如果你檢視資料檔案相關的資料字典是找不到臨時檔案的身影的。

select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME

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

SYSTEM                         /u03/ora11g/oradata/TEST01/system01.dbf

SYSAUX                         /u03/ora11g/oradata/TEST01/sysaux01.dbf

UNDOTBS                        /u03/ora11g/oradata/TEST01/undotbs01.dbf

TEST_DATA1                     /u02/ora11g/testdata1.dbf

POOL_DATA                      /u03/ora11g/oradata/TEST01/pool_data03.dbf

POOL_DATA                      /u03/ora11g/oradata/TEST01/pool_data01.dbf

POOL_DATA                      /u03/ora11g/oradata/TEST01/pool_data02.dbf

POOL_DATA                      /u03/ora11g/oradata/TEST01/pool_data04.dbf

POOL_DATA                      /u03/ora11g/oradata/TEST01/pool_data05.dbf

POOL_DATA                      /u01/ora11g/pool_data06.dbf

POOL_DATA                      /u01/ora11g/pool_data07.dbf

TEST_DELETE                    /u01/ora11g/test_delete.dbf

TEST                           /u01/ora11g/test01.dbf

13 rows selected.

隻能夠從專有的資料字典dba_temp_files中查到。之前做過一些使用rman的備份恢複，哪怕是全庫恢複，最後臨時檔案也不包含，這個臨時檔案還是需要單獨建立。因為它臨時的角色。

SQL> select file_name,tablespace_name ,bytes,status from dba_temp_files;

FILE_NAME                                          TABLESPACE_NAME                     BYTES STATUS

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

/u03/ora11g/oradata/TEST01/temp01.dbf              TEMPTS1                         419430400 ONLINE

我們來簡單示範一下臨時檔案的玄機，首先在建立一個新的臨時表空間的時候，我們檢視磁盤空間情況。發現還剩餘13M左右。

[ora11g@rac1 TEST01]$ df -k|grep u03

/dev/sdb3              7906196   7490784     13788 100% /u03

這個時候我們建立一個臨時表空間，定義大小為10M,這個時候剩餘空間應該為2~3M左右。

SQL> create temporary tablespace tempts2 tempfile '/u03/ora11g/oradata/TEST01/temp02.dbf' size 10M;

Tablespace created.

但是實際上空間還剩餘很多，隻占用了很小的一部分空間。

SQL> !df -k|grep u03

/dev/sdb3              7906196   7491820     12752 100% /u03

但是奇怪的是檢視臨時檔案的大小，卻是實實在在的10M.

[ora11g@rac1 TEST01]$ ll temp02.dbf

-rw-r----- 1 ora11g dba 10493952 Feb 20 06:35 temp02.dbf

這種檔案叫做稀疏檔案，也就是說在需要使用之前不會占用真正的存儲空間。

說完了臨時表空間，臨時檔案，可以擴充說一下臨時表空間組。

臨時表空間的好處也比較明顯，主要就是把多個臨時表空間放在一起用，這樣就可以完成一些略微複雜的功能。主要功能是：

    防止一個臨時表空間出現空間不足的問題。

    同一個使用者同時連接配接多個session時可以使用不同的臨時表空間。

    在并行操作中可以并行使用多個臨時表空間。

至于建立的過程也是很簡單的，比如我們新建立一個臨時表空間tempts2，然後把已有的臨時表空間tempts1和信建立的tempts2都放到臨時表空間組temp中。

這個時候檢視，沒有發現表空間組的存在。

SQL> select * from dba_tablespace_groups;

no rows selected

我們可以簡單的使用alter tablespace語句把臨時表空間放入對應的組中。

SQL> alter tablespace tempts2 tablespace group temp;

Tablespace altered.

SQL> alter tablespace tempts1 tablespace group temp;

檢視臨時表空間組的情況

GROUP_NAME                     TABLESPACE_NAME

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

TEMP                           TEMPTS1

TEMP                           TEMPTS2

如果要取消也很容易。

SQL> alter tablespace tempts2 tablespace group '';

我們再來說說最後的要點臨時表，

臨時表主要用于存放食物或者會話中的臨時結果集。比如排序和hash操作的結果，臨時表中的資料隻對目前會話可見，在這一點上臨時表又分為兩種，在具體使用的時候可以靈活的使用。

我們建立兩個臨時表，不同之處就在于标黃的部分。

SQL> create global temporary table temp_session on commit preserve rows as select *from user_tables where rownum

Table created.

SQL> create global temporary table temp_transaction on commit delete rows as select *from user_tables where rownum

 temp_session是基于會話級别的，在同一個會話中，送出之後資料還是存在，除非指定了delete或者truncate操作，而temp_transaction是基于事務層面的，在commit的時候就會自動清空資料。

我們來簡單的看一下，在同一個session中。檢視temp_session的資料情況。在temp_session中因為還是目前session，是以資料還是存在可見，但是temp_transaction中資料就不可見。因為ddl相當于auto commit的事物已經完成了。

SQL> select count(*)from temp_session;

  COUNT(*)

----------

         4

SQL> select count(*)from temp_transaction;

 我們嘗試删除temp_session中的資料。

SQL> delete from temp_session;

4 rows deleted.

然後做回退操作。

SQL> rollback;

Rollback complete.

發現資料還是能夠回退回來。

斷開連接配接

 SQL> disc

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn n1/n1

Connected.

資料就都沒了。

臨時表的使用還是比較廣泛的，而且臨時表中的資料都是對目前session可見，有session級和事務級的生命周期，不存在并發的困擾。合理的使用還是能夠發揮很好的效果。