ORACLE臨時表總結

臨時表概念

臨時表就是用來暫時儲存臨時資料（亦或叫中間資料）的一個資料庫物件，它和普通表有些類似，然而又有很大區別。它只能儲存在臨時表空間，而非使用者的表空間。ORACLE臨時表是會話或事務級別的，只對當前會話或事務可見。每個會話只能檢視和修改自己的資料。

臨時表語法

臨時表分類

ORACLE臨時表有兩種型別：會話級的臨時表和事務級的臨時表。

1）ON COMMIT DELETE ROWS

它是臨時表的預設引數，表示臨時表中的資料僅在事物過程（Transaction）中有效，當事物提交（COMMIT）後，臨時表的暫時段將被自動截斷（TRUNCATE），但是臨時表的結構 以及後設資料還儲存在使用者的資料字典中。如果臨時表完成它的使命後，最好刪除臨時表，否則資料庫會殘留很多臨時表的表結構和後設資料。

2）ON COMMIT PRESERVE ROWS

它表示臨時表的內容可以跨事物而存在，不過，當該會話結束時，臨時表的暫時段將隨著會話的結束而被丟棄，臨時表中的資料自然也就隨之丟棄。但是臨時表的結構以及後設資料還儲存在使用者的資料字典中。如果臨時表完成它的使命後，最好刪除臨時表，否則資料庫會殘留很多臨時表的表結構和後設資料。

1:會話級的臨時表的資料和你當前會話有關係，當前SESSION不退出的情況下，臨時表中的資料就還存在，臨時表的資料只有當你退出當前SESSION的時候才被截斷（TRUNCATE TABLE），如下所示：

會話級別的臨時表建立：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT PRESERVE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS

AS

SELECT * FROM TEST;

操作示例：

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

 (

　　 ID NUMBER ,

 　　NAME VARCHAR2(32)

 ) ON COMMIT PRESERVE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

    SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　NAME

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

1 　　　　　　　　kerry

SQL> INSERT INTO TMP_TEST

 　　SELECT 2, 'rouce' FROM DUAL;

1 row inserted

SQL> ROLLBACK;

Rollback complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

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

1 　　　　　　　　　　kerry

SQL>

2:事務級的臨時表（預設），這種型別的臨時表與事務有關，當進行事務提交或者事務回滾的時候，臨時表的資料將自行截斷，即當COMMIT或ROLLBACK時，資料就會被TRUNCATE掉，其它的特性和會話級的臨時表一致。

事務級臨時表的建立方法：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
 　　　　ID NUMBER ,
 　　　　NAME VARCHAR2(32)

 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

 　　SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

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

1 　　　　　　　　　　kerry

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　  NAME

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

SQL>

3:關於臨時表只對當前會話或事務可見。每個會話只能檢視和修改自己的資料。

用DM使用者登入資料庫，開啟SESSION 1後，建立臨時表TMP_TEST

CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

 (

 　　　　ID NUMBER ,

 　　　　NAME VARCHAR2(32)

 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

　　 SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

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

1 kerry

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

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

SQL>

用sys使用者登入資料庫，開啟SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到臨時表資料

SELECT * FROM DM.TMP_TEST; --查不到資料，即使TMP_TEST臨時表存在資料。

臨時表與永久表區別

SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",

　　  "TEMPORARY", DURATION, "MONITORING"

　　 FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;

TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING

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

TEST          TBS_EDS_DATA    YES       N                      YES

TMP_TEST                      NO        Y     SYS$SESSION       NO

如上所示，臨時表是儲存在臨時表空間裡面的，但是上面指令碼可以看出，臨時表在資料字典中沒有指定其表空間，臨時表是NOLOGGING，DURATION為SYS$SESSION

臨時表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少其實在應用中，往往會建立一個NOLOGGING的永久表（中間表）來儲存中間資料，從而代替臨時表，至於這這兩者有啥優劣，真是很難說清道明(歡迎大家探討)。

臨時表用途

什麼時候使用臨時表？用臨時表和用中間表有啥區別呢？

我覺得是在需要的時候應用，下面是David Dai關於臨時表的一個應用說明，我覺得非常形象的說明了臨時表的應用場景：對於一個電子商務類網站，不同消費者在網站上購物，就是一個獨立的 SESSION，選購商品放進購物車中，最後將購物車中的商品進行結算。也就是說，必須在整個SESSION期間儲存購物車中的資訊。同時，還存在有些消費者，往往最終結賬時放棄購買商品。如果，直接將消費者選購資訊存放在最終表（PERMANENT）中，必然對最終表造成非常大的壓力。因此，對於這種案例，就可以採用建立臨時表(ON COMMIT PRESERVE ROWS)的方法來解決。資料只在 SESSION 期間有效，對於結算成功的有效資料，轉移到最終表中後，ORACLE自動TRUNCATE 臨時資料；對於放棄結算的資料，ORACLE 同樣自動進行 TRUNCATE ，而無須編碼控制，並且最終表只處理有效訂單，減輕了頻繁的DML操作的壓力。

1：當處理某一批臨時資料，需要多次DML操作時（插入、更新等），建議使用臨時表。

2：當某些表在查詢裡面，需要多次用來做連線時。（為了獲取目標資料需要關聯A、B、C， 同時為了獲取另外一個目標資料，需要關聯D、B、C….）

關於臨時表和中間表（NOLOGGING，儲存中間資料，使用完後刪除）那個更適合用來儲存中間資料，我個人更傾向於使用臨時表，而不建議使用中間表。

注意事項

1 ) 不支援 lob 物件，這也許是設計者基於執行效率的考慮，但實際應用中確實需要此功能時就無法使用臨時表了。這點網上很多資料都這麼說，我沒有追查到底是那個版本不支援lob物件，至少在ORACLE 10g這個版本中，臨時表是支援lob物件的.

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

2 (

3 ID NUMBER ,

4 NAME CLOB

5 ) ON COMMIT PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TMP_TEST

2 SELECT 1, 'ADF' FROM DUAL;

1 row inserted

SQL> SELECT * FROM V$VERSION;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2 ) 不支援主外來鍵關係

3 ）臨時表不能永久的儲存資料。

4 ）臨時表的資料不會備份，恢復，對其的修改也不會有任何日誌資訊

5 ）臨時表不會有DML 鎖

DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

6 ）儘管對臨時表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少。請見官方文件：

DML statements on temporary tables do not generate redo logs for the data changes.However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination,either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

7 ） 臨時表可以建立臨時的索引、檢視、觸發器。

8 ) 如果要DROP會話級別臨時表，並且其中包含資料時，必須先截斷其中的資料。否則會報錯。

SQL> DROP TABLE TMP_TEST PURGE;

DROP TABLE TMP_TEST PURGE

ORA-14452: 試圖建立, 更改或刪除正在使用的臨時表中的索引

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped

---

使用Oracle基於session的臨時表要注意的問題

在Oracle中有兩種臨時表，一種是基於事務,就是當出現commit之後，資料就會被清空；一種是基於session，當session斷開後，資料會被清空。

--基於事務的臨時表

create global temporary table test
(
  ID   number
)
on commit delete rows;


--基於session的臨時表
create global temporary table test
(
  ID   number
)
on commit preserve rows;

如果你的系統架構是三層架構，有連線池的，如 weblogic+oracle這種架構，其實session是不會退出的，只是請求完畢後，將session還給連線池。如果你操作基於session的臨時表，每次操作完表後不做刪除操作，那資料會累積。最好的做法是使用完以後truncate，今天在測試環境上使用系統測試發現的問題。