CNDBA社区
1. 说明
私有临时表会在事务或会话结束时自动删除。私有临时表存储在内存中，仅对创建它的会话可见。
私有临时表仅作用在会话或事务上，从而在应用程序编码方面提供了更大的灵活性，从而使代码维护更加容易，并具有更好的即时可用功能。私有临时表命名必须以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
2. 创建私有临时表
默认情况下，私有临时表存储在创建用户的默认临时表空间中，也可以指定其他临时表空间。
| 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. |
2.1. 创建一个用于事务的私有临时表
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
(time_id DATE,
amount_sold NUMBER(10,2))
ON COMMIT DROP DEFINITION;
2.2. 创建一个用于会话的私有临时表
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
版权声明：本文为博主原创文章，未经博主允许不得转载。
私有临时表
