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

来源：这里教程网时间：2026-03-03 12:13:47 作者：

[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 --//这样私有临时表全部看不见了.