18c private temporary table

oracle中临时表很常见，它是用来只在一个事务或者会话时间段内存放临时数据的表。临时表中的数据对于会话来说是私有的，每个会话只能看到和处理它自己的临时数据。

临时表

在18c以前，我们所碰到的临时表一般都成为全局临时表，都是通过CREATE TEMPORARY TABLE的方式新建，所有的会话都能看到这个临时表的字典信息。而从18c开始引入了一种新的临时表，成为私有临时表，只有会话自己能看到它的私有临时表的字典信息。

对于这两种临时表的主要区别，可以概况如下:

特性	全局临时表	私有临时表
命名规则	与永久表一样	必须以ORA$PTT_开头
表定义可见	所有会话	只有创建临时表的会话
表定义的存放	磁盘	内存
种类	基于事务(ON COMMIT DELETE ROWS)
基于会话(ON COMMIT PRESERVE ROWS)	基于事务(ON COMMIT DELETE ROWS)
基于会话(ON COMMIT PRESERVE ROWS)

这里其实还有第三种临时表，基于游标周期的临时表，主要应用于oracle为了优化性能自动创建储存在内存中的临时表，比如with as转换过程中生成的。

私有临时表特性

由于私有临时表的字典和数据只能被创建它的会话所看到，基于这个特性，在下面的场景中就很适合建私有临时表

• 当一个应用需要临时存放数据，只插入一次读取多次，最后事务或会话结束后就删除表
• 当一个会话处于不确定状态并且必须要给不同的事务创建不同的临时表
• 当临时表的创建不能重新开启新的事务或者不能提交已有事务
• 当相同用户的不同会话必须使用同一个临时表的名称
• 当只读数据库需要临时表

例如在某个报表应用中只只用一个用户，但是应用会使用这个用户建立多个连接去生成不同的报表。每个会话都使用私有临时表来计算不同的事务，每个会话创建的临时表名称都一样。当每个事务提交时，它的临时数据就不再需要了。

私有临时表有两种不同的类型，决定了数据和表定义什么时候会被删除。这里根据提交时的处理的参数不同，可以做如下区分

ON COMMIT设置	含义
DROP DEFINITION	创建基于事务的私有临时表，在事务结束的时候表定义和数据都会被删除
PRESERVE DEFINITION	创建基于会话的私有临时表，在创建这个表的会话结束时表定义和数据才会被删除

创建私有临时表

必须要以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

在默认情况下，私有临时表是存放在创建表用户的默认临时表空间下。但是在创建表的时候也可以通过指定TABLESPACE关键字来替代。

同样私有临时表也支持CTAS的创建方式

create private temporary table ora$ptt_tmp_xb 
as 
select * from user_tables;

PL/SQL中的私有临时表

由于私有临时表的定义是无法持久存在的，当在存储过程或函数等中进行编译的时候，表是不存在的。所以如果要在存储过程中使用私有临时表的话，只能通过动态sql的方式。

create or replace function p_test(id in number) return varchar2
as
 v_sql varchar2(3000);
 v_return varchar2(50);
begin
  v_sql :='create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10)) on commit drop definition';
  execute immediate v_sql;
  execute immediate q'{insert into ora$ptt_tmp_xb values (1,'xb')}';
  execute immediate 'select name into :v_return from ora$ptt_tmp_xb where id =:id' into v_return using id;
  return v_return;
 end;
 /

调用这个函数，可以正常返回

set serveroutput on
begin
  dbms_output.put_line('p_test(1) = ' || p_test(1));
end;
/

p_test(1) = xb

PL/SQL procedure successfully completed.

查询视图

因为私有临时表都是存放在内存当中的，所以在数据字典里是无法查到的。但是你可以通过USER_TABLES视图来查询当前会话中的所有私有临时表。

• DBA_PRIVATE_TEMP_TABLES : 数据库中所有私有临时表
• USER_PRIVATE_TEMP_TABLES： 当前会话中所有私有临时表

限制

私有临时表除了有全局临时表所有的限制外，还有一些其他的：

• 必须以PRIVATE_TEMP_TABLE_PREFIX参数设置的前缀作为临时表的开头，默认是ORA$PTT_
• 持久对象不能直接引用私有临时表
• 私有临时表不能包含索引、物化视图等
• 私有临时表不能有主键或者其他索引相关的约束
• 字段不能有默认值
• 私有临时表不能通过DBLINK访问