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;

注：SYS用户无法创建私有临时表，原因不清楚。

请查看文档：https://www.cndba.cn/Expect-le/article/2949