[20181112]Private Temporary Tables Oracle Database 18C.txt

[20181112]Private Temporary Tables Oracle Database 18C.txt

--//18C出現了一種新的臨時表，稱為私有臨時表。它們是在事務或會話結束時刪除的臨時資料庫物件。私有臨時表儲存在記憶體中，每個

--//臨時表只對建立它的會話可見。

--//語法如下：

CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT DROP DEFINITION ;

or

CREATE PRIVATE TEMPORARY TABLE .... ON COMMIT PRESERVE DEFINITION;

DROP DEFINITION     : This creates a private temporary table that is transaction specific. All data in the table is

                      lost, and the table is dropped at the end of transaction.

PRESERVE DEFINITION : This creates a private temporary table that is session specific. All data in the table is lost,

                      and the table is dropped at the end of the session that created the table.

--//Private temporary table name 定義使用字首按照引數private_temp_table_prefix

--//簡單透過例子說明：

1.環境：

SQL> select banner_full from v$version;

BANNER_FULL

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

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> set linesize 2000

SQL> show parameter private

NAME                      TYPE    VALUE

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

private_temp_table_prefix string  ORA$PTT_

--//session 1:

CREATE PRIVATE TEMPORARY TABLE temp_test

( 

  id number;

  create_date date

) 

ON COMMIT PRESERVE DEFINITION;

*

ERROR at line 1:

ORA-00903: invalid table name

--//字首必須使用private_temp_table_prefix。

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_test

( 

  id number,

  create_date date

) 

ON COMMIT DROP DEFINITION;

Table created.

SQL> SELECT sid, serial#, owner, table_name, duration  FROM user_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       392      63118 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

2.繼續測試：

--//別的會話也可以定義相同的表名：

--//session 2:

SQL> SELECT sid, serial#, owner, table_name, duration FROM user_private_temp_tables;

no rows selected

--//別的會話看不到對方建立的私有臨時表.

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_test

( 

  id number,

  c_date date

) 

ON COMMIT DROP DEFINITION;

Table created.

--//注:欄位c_date與前面不同.實際上表結構完全不同也可以的.

SQL> SELECT sid, serial#, owner, table_name, duration FROM user_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       196       7874 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

--//檢視user_private_temp_tables僅僅看到自己定義的.

SQL> SELECT sid, serial#, owner, table_name, duration   FROM dba_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       196       7874 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

       392      63118 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

--//按照定義ON COMMIT DROP DEFINITION的特點，應該是commit後刪除該表。

--//session 1:

SQL> insert into ORA$PTT_temp_test values (1,sysdate);

1 row created.

SQL> select count(*) from ORA$PTT_temp_test;

  COUNT(*)

----------

         1

SQL> commit ;

Commit complete.

SQL> select count(*) from ORA$PTT_temp_test;

select count(*) from ORA$PTT_temp_test

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

--//可以發現提交後,表定義消失.

3.測試 ON COMMIT PRESERVE DEFINITION:

--//按照定義就是提交後保持表定義，直到退出會話才消失。

--//session 1:

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_test

( 

  id number,

  create_date date

) 

ON COMMIT PRESERVE DEFINITION;

Table created.

SQL> insert into ORA$PTT_temp_test values (1,sysdate);

1 row created.

SQL> commit ;

Commit complete.

SQL> select count(*) from ORA$PTT_temp_test;

  COUNT(*)

----------

         1

--//提交後,表定義還在.繼續dml操作:

SQL>  insert into ORA$PTT_temp_test values (2,sysdate);

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from ORA$PTT_temp_test ;

        ID CREATE_DATE

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

         1 12-NOV-18

         2 12-NOV-18

--//session 2:

SQL> SELECT sid, serial#, owner, table_name, duration   FROM dba_private_temp_tables;

       SID    SERIAL# OWNER                          TABLE_NAME                     DURATION

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

       196       7874 SYSTEM                         ORA$PTT_TEMP_TEST              TRANSACTION

       392      63118 SYSTEM                         ORA$PTT_TEMP_TEST              SESSION

--//可以發現ON COMMIT PRESERVE DEFINITION;的表DURATION=SESSION.

--//        ON COMMIT DROP DEFINITION;的表DURATION=TRANSACTION.

--//全部退出後,查詢:

SQL>  SELECT sid, serial#, owner, table_name, duration   FROM dba_private_temp_tables;

no rows selected

--//這樣私有臨時表全部看不見了.