Oracle｜Oracle 19c中的私有临时表

安呀智数据坊 2025-05-02

注: 本文为安丫科技焱焱枫的原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

导

语

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
SQL>

创建私有临时表

如果您使用过全局临时表，那么创建私有临时表的语法看起来会很熟悉。

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 ... 语句填充表。除了这一种情况之外，您只能对私有临时表使用动态采样。

写在最后

在信息高速流动的今天，数据是企业最宝贵的资产，也是创新的原动力。

安呀智数据坊专注于提供高效的数据库优化服务与定制化培训，帮助您轻松掌握数据资产，释放技术潜能。

让我们携手，用专业与创新，开启企业智能转型的新篇章！

END

关键词回复(可见相应文章):

oracle、mysql、pg、postgresql、sql、性能优化、故障处理、数据迁移、备份恢复、版本升级、补丁管理、深度巡检、解决方案、架构设计......

小助手

有任何问题或疑问，欢迎加V进群探讨哦～

数据库 oracle

文章转载自安呀智数据坊，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle｜Oracle 19c中的私有临时表

评论