Oracle 18c 新特性-私有臨時表

說明

私有臨時表會在事務或會話結束時自動删除。私有臨時表存儲在記憶體中，僅對建立它的會話可見。

有臨時表僅作用在會話或事務上，進而在應用程式編碼方面提供了更大的靈活性，進而使代碼維護更加容易，并具有更好的即時可用功能。私有臨時表命名必須以ORA$PTT_為字首，是由參數private_temp_table_prefix控制。

私有臨時表适用場景：

a) When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session

b) When a session is maintained indefinitely and must create different temporary tables for different transactions

c) When the creation of a temporary table must not start a new transaction or commit an existing transaction

d) When different sessions of the same user must use the same name for a temporary table

e) When a temporary table is required for a read-only database

建立私有臨時表

預設情況下，私有臨時表存儲在建立使用者的預設臨時表空間中，也可以指定其他臨時表空間。

ON COMMIT Setting	Implications
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.

建立一個用于事務的私有臨時表

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
    (time_id      DATE,
     amount_sold  NUMBER(10,2))
   ON COMMIT DROP DEFINITION;      

建立一個用于會話的私有臨時表

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_session
    (time_id      DATE,
     amount_sold  NUMBER(10,2))
   ON COMMIT PRESERVE DEFINITION;