oracle中临时表很常见，它是用来只在一个事务或者会话时间段内存放临时数据的表。临时表中的数据对于会话来说是私有的，每个会话只能看到和处理它自己的临时数据。
临时表
在18c以前，我们所碰到的临时表一般都成为全局临时表，都是通过CREATE TEMPORARY TABLE的方式新建，所有的会话都能看到这个临时表的字典信息。而从18c开始引入了一种新的临时表，称为私有临时表，只有会话自己能看到它的私有临时表的字典信息。
对于这两种临时表的主要区别，可以概况如下:
这里其实还有第三种临时表，基于游标周期的临时表，主要应用于oracle为了优化性能自动创建储存在内存中的临时表，比如with as转换过程中生成的。
GTT 和 PTT 的主要区别如下：
GTT 更早。GTT 是 8i 就有的特性，PTT 是 18c 才有的特性。
GTT 和 PTT 数据都是会话私有的，而且会话结束后数据就没有了，但 GTT 的定义是全局的。这里的全局指的是表的定义，不会随着会话结束而删除表结构。
事务提交时，GTT 可以选择是否保留数据，PTT 可以选择是否保留定义。
GTT 的表定义是所有会话共享的；PTT 的表定义是会话私有的。
GTT 表命名没有强制要求，PTT 要求以开头前缀是 ORA$PTT_ ,否则会报错。
下面我们来仔细介绍一下这两种临时表的区别
全局临时表（Global Temporary Table）
创建全局临时表
全局临时表中的数据是私有的，因此会话插入的数据只能由该会话访问。全局临时表中特定于会话的行可以为整个会话保留，也可以仅为当前事务保留。
ON COMMIT DELETE ROWS 子句指示应在事务结束或会话结束时删除数据。
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
id NUMBER,
description VARCHAR2(20)
)
ON COMMIT DELETE ROWS;
-- Insert, but don't commit, then check contents of GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE');
SELECT COUNT(*) FROM my_temp_table;
COUNT(*)
----------
1
SQL> COMMIT;
SELECT COUNT(*) FROM my_temp_table;
COUNT(*)
----------
0相反，ON COMMIT PRESERVE ROWS 子句指示行应在事务结束后继续保留。它们只会在会话结束时被删除。
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
id NUMBER,
description VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;
INSERT INTO my_temp_table VALUES (1, 'ONE');
COMMIT;
SELECT COUNT(*) FROM my_temp_table;
COUNT(*)
----------
1
SQL> CONN test/test
SELECT COUNT(*) FROM my_temp_table;
COUNT(*)
----------
0私有临时表（Private Temporary Table）
私有临时表特性
由于私有临时表的字典和数据只能被创建它的会话所看到，基于这个特性，在下面的场景中就很适合建私有临时表
当一个应用需要临时存放数据，只插入一次读取多次，最后事务或会话结束后就删除表
当一个会话处于不确定状态并且必须要给不同的事务创建不同的临时表
当临时表的创建不能重新开启新的事务或者不能提交已有事务
当相同用户的不同会话必须使用同一个临时表的名称
当只读数据库需要临时表
例如在某个报表应用中只只用一个用户，但是应用会使用这个用户建立多个连接去生成不同的报表。每个会话都使用私有临时表来计算不同的事务，每个会话创建的临时表名称都一样。当每个事务提交时，它的临时数据就不再需要了。
私有临时表有两种不同的类型，决定了数据和表定义什么时候会被删除。这里根据提交时的处理的参数不同，可以做如下区分
创建私有临时表
必须要以ora$ptt_作为开头，否则会报错
SQL> create private temporary table tmp_xb (id number,name varchar2(10));
create private temporary table tmp_xb (id number,name varchar2(10))
*
ERROR at line 1:
ORA-00903: invalid table name创建基于事务的临时表，会在事务结束的时候删除表定义
create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10))
on commit drop definition;
SQL> insert into ora$ptt_tmp_xb values(1,'xb');
1 row created.
SQL> select * from ora$ptt_tmp_xb;
ID NAME
---------- ----------
1 xb
SQL> commit;
Commit complete.
SQL> select * from ora$ptt_tmp_xb;
select * from ora$ptt_tmp_xb
*
ERROR at line 1:
ORA-00942: table or view does not exist而换成基于会话的时候，就仍然可以查到。只有等到重连的时候表才会被删除
create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10))
on commit preserve definition;
Table created.
SQL> insert into ora$ptt_tmp_xb values(1,'xb');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ora$ptt_tmp_xb;
ID NAME
---------- ----------
1 xb
# 重连
SQL> conn xb/xb@pdb18c
Connected.
SQL> select * from ora$ptt_tmp_xb;
select * from ora$ptt_tmp_xb
*
ERROR at line 1:
ORA-00942: table or view does not exist限制
私有临时表除了有全局临时表所有的限制外，还有一些其他的：
必须以PRIVATE_TEMP_TABLE_PREFIX参数设置的前缀作为临时表的开头，默认是ORA$PTT_
持久对象不能直接引用私有临时表
私有临时表不能包含索引、物化视图等
私有临时表不能有主键或者其他索引相关的约束
字段不能有默认值
私有临时表不能通过DBLINK访问
全局临时表与 Redo，Undo 的关系
全局临时表在设计上为了最小化日志记录，所以是不生产 REDO 日志的。因为 Redo 日志是用于数据库恢复的，而临时表的数据本身就是临时的，不需要恢复，会话结束后数据就消失了，自然不需要生成 Redo 日志。虽然数据本身不产生 Redo，但是对临时表的 Undo 会记录 Redo。UNDO数据也是需要保护的，否则在系统崩溃时无法保证事务回滚的一致性。因此，为UNDO块生成的REDO是无法完全避免的。不过，这个量通常非常小。
但是会生成 Undo，对临时表的 DML 操作会生成 Undo 数据，这是为了保证事务的 ACID 属性。主要是为了事务回滚和读一致性。
在12c，可以将undo写到临时表空间，这样可以大大减少 redo 与 undo。
写入undo表空间需要数据库以读写方式打开，所以不能在只读数据库和物理备数据库中使用全局临时表。
全局临时表包含临时数据，在恢复场景中不需要这些数据，因此使用redo保护它们意味着对系统造成不必要的额外负载。
与全局临时表关联的undo增加了满足undo保留期所需的总空间。
此行为由参数控制，默认不启用：
SQL> show parameter TEMP_UNDO_ENABLED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSEALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
SET AUTOTRACE ON STATISTICS;
create GLOBAL TEMPORARY table gtt_test(a int);
insert into gtt_test select 1 from dual connect by level < 10000;
9999 rows created.
Statistics
----------------------------------------------------------
11 recursive calls
143 db block gets
25 consistent gets
0 physical reads
280 redo size
195 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9999 rows processed
SQL>
SELECT t.used_ublk,
t.used_urec
FROM v$transaction t,
v$session s
WHERE s.saddr = t.ses_addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
USED_UBLK USED_UREC
---------- ----------
1 1和未开启参数前相比，undo和redo都下降了很多。
参考文档：
https://www.xbdba.com/2019/12/26/18c-private-temporary-table/
https://www.xbdba.com/2019/12/26/18c-private-temporary-table/
评论