【Oracle OCP】Oracle 19c之私有临时表

本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

Oracle 19C介绍了一个私人临时表的概念，私有临时表的概念是基于内存的临时表，该表在会话结束时或事务结束时取决于设置。

如果您从不同的数据库引擎（例如 SQL Server）转向 Oracle，您可能会对 Oracle 的临时表概念感到困惑。在SQL Server中，开发人员会定期创建临时表来完成一些工作并删除它。在 Oracle 中，全局临时表 (GTT) 是一个永久元数据对象，它在特定于事务或特定于会话的基础上将行保存在临时段中。动态创建和删除 GTT 被认为是不正常的。随着私有临时表的引入，Oracle 有了一个类似于其他引擎中的选项，其中表对象本身是临时的，而不仅仅是数据。

一、临时表类型

Oracle 支持两种类型的临时表。

• 全局临时表：自 Oracle 8i 起可用。这里讨论过。

• 私有临时表：从 Oracle 18c 开始可用，也是本文的主题。

二、私有临时表的命名

PRIVATE_TEMP_TABLE_PREFIX 初始化参数默认为“ORA$PTT_”，定义创建私有临时表时名称中必须使用的前缀。在下面的示例中，我们创建一个私有临时表，但未在名称中使用正确的前缀，这会导致错误。

CREATE PRIVATE TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
);

CREATE PRIVATE TEMPORARY TABLE my_temp_table (
                  *
ERROR at line 1:
ORA-00903: invalid table name

三、创建私有临时表

如果您使用过全局临时表，那么创建私有临时表的语法看起来会很熟悉。
ON COMMIT DROP DEFINITION 子句（默认值）指示应在事务结束或会话结束时删除表。

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  id      NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;

-- Insert, but don't commit, then check contents of PTT.
INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM ora$ptt_my_temp_table;
 
  COUNT(*)
----------
     1
SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

SELECT COUNT(*) FROM ora$ptt_my_temp_table
            *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

相反，ON COMMIT PRESERVE DEFINITION 子句指示表和任何数据应在事务结束后持续存在。该表将在会话结束时删除。

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
  id      NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE DEFINITION;

-- Insert, but don't commit, then check contents of PTT.
INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');

SELECT COUNT(*) FROM ora$ptt_my_temp_table;
  COUNT(*)
----------
     1
SQL>

-- Commit and check contents.
COMMIT;

SELECT COUNT(*) FROM ora$ptt_my_temp_table;
  COUNT(*)
----------
     1
SQL>

-- Reconnect and check contents of GTT.
CONN test/test@pdb1
SELECT COUNT(*) FROM ora$ptt_my_temp_table;

SELECT COUNT(*) FROM ora$ptt_my_temp_table
            *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

上面的输出显示该表在提交后仍然存在，但在我们断开连接并创建新会话时被删除。
我们还可以使用 CTAS 方法创建私有临时表。

CREATE PRIVATE TEMPORARY TABLE ora$ptt_emp AS
SELECT * FROM emp;

四、私有临时表和PL/SQL

永久 PL/SQL 对象直接引用临时对象是没有意义的，因为它在编译时不存在。如果您想使用永久对象中的私有临时表，则必须使用动态 SQL 来完成。以下愚蠢的示例创建一个使用私有临时表的存储函数。

CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER)
RETURN VARCHAR2
AS
  l_sql     VARCHAR2(32767);
  l_return  VARCHAR2(30);
BEGIN
  l_sql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (
        id        NUMBER,
        description     VARCHAR2(20)
      )
      ON COMMIT DROP DEFINITION';
 EXECUTE IMMEDIATE l_sql;
 EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE')]';
 EXECUTE IMMEDIATE 'SELECT description INTO :l_return FROM ora$ptt_my_temp_table WHERE id = :id' INTO l_return USING p_id;
 RETURN l_return;
END;
/

该函数在19C中无法按预期工作。

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.put_line('ptt_test(1) = ' || ptt_test(1));
END;
/
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
ORA-06512: at "SYS.PTT_TEST", line 13
ORA-06512: at line 2
SQL>

五、相关视图

私有临时表是基于内存的，因此数据字典中没有记录任何元数据。因此，您无法使用 USER_TABLES 视图来显示当前会话中的私有临时表的列表。以下视图可用于显示有关私有临时表的信息。

• DBA_PRIVATE_TEMP_TABLES ：数据库中的所有私有临时表。

• USER_PRIVATE_TEMP_TABLES ：当前会话中的私有临时表。

六、使用限制

私有临时表具有全局临时表的限制（请参阅此处），但也有其他限制。

• 表名必须以 PRIVATE_TEMP_TABLE_PREFIX 初始化参数中定义的前缀开头。默认值为“ORA$PTT_”。

• 永久对象不能直接引用私有临时表。

• 私有临时表上不允许使用索引、物化视图和区域映射。

• 私有临时表上不允许使用主键或任何需要索引的约束。

• 列不能有默认值。

• 无法通过数据库链接访问私有临时表。

• 您无法收集私有临时表的统计信息。显示统计信息的唯一方法是在创建时使用 CREATE TABLE ... AS SELECT ... 语句填充表。除了这一种情况之外，您只能对私有临时表使用动态采样。

•