在Oracle数据库中创建表提交后如何回滚

在Oracle数据库中运行DDL时，它会发出两个隐式提交。语句之前一个，语句完成之后一个（成功！）。

上周，我在Twitter上提出了以下测验，违反了这一规则。

我运行以下命令：

• 建立表格
• 在其中插入一行
• 创建另一个表
• 回滚
• 现在第一个表是EMPTY！

怎么样？！

为了帮助您，以下是我使用一些代码删除的脚本：

create
table my_tab (
  c1 int
)
/
 
insert into my_tab values ( 1 );
   
create
table ora$my_tab (
  c1 int
) 
/
   
rollback;
 
select count (*) from my_tab;
复制

您需要添加什么代码，以便计数返回零？

以下是此挑战的规则：

• 所有代码在单个会话中运行
• 所有语句成功（没有错误）
• 没有其他物体在玩（没有触发器，工作或其他恶作剧）
• 在创建第一个表和最后一个查询之间没有其他语句

稍等片刻，考虑这是怎么可能的。至少有三种方式。

有解决方案吗？

这是三个：

创建一个临时表

我最初的解决方案是将第二个表设为私有临时表（PTT）。这些已在Oracle Database 18c中添加，不再具有隐式提交！

这意味着在创建PTT之前运行的任何DML都不会提交。因此，执行回滚时，它将撤消插入操作。

要使用PTT，其名称的开头必须与为private_temp_table_prefix参数设置的值匹配。

因此，要使脚本正常工作，必须事先将其更改为ora $ my_！

给出以下最终解决方案：

alter session set private_temp_table_prefix = ora$my_ deferred;
 
<restart the database> 
 
create
table my_tab (
  c1 int
)
/
 
insert into my_tab values ( 1 );
 
create private temporary
table ora$my_tab (
  c1 int
)
/
   
rollback;
 
select count (*) from my_tab;
复制

如果运行此命令，请确保在完成后将private_temp_table_prefix设置回ora $ ptt_！

创建一个全局临时表

虽然PTT是我想要的解决方案，但Tobias Wirtz很快就分享了另一个解决方案。您可以通过将第一个表设为全局临时表（GTT）来获得这种效果！

这有一个默认子句on commit delete rows。

因此，当创建第二张表时隐式提交生效时，GTT会清空自身！

如果您想亲自尝试以下示例：

create global temporary
table my_tab (
  c1 int
)
/
 
insert into my_tab values ( 1 );
 
create
table ora$my_tab (
  c1 int
)
/
   
rollback;
 
select count (*) from my_tab;
复制

使用自主交易

另一个巧妙的解决方案来自Hans Jakob Schelbeck-Pedersen。他指出，您可以将第二个create表包装在一个自治事务中。这将提交并回滚，而与父事务无关。

因此，即使第二个create表确实提交了，这也只剩下原始事务。因此，您仍然可以将其回滚。如果您需要在18c之前的事务中途创建表，则可以使用此方法。

如果您想尝试一下：

create
table my_tab (
  c1 int
)
/
 
insert into my_tab values ( 1 );
 
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
execute immediate '
create 
table ora$my_tab (
  c1 int
)
'; 
END;
/
   
rollback;
 
select count (*) from my_tab;
复制

总结

专用临时表违反了Oracle数据库中“ DDL始终提交”的长期规则。您过去学到的东西可能不再是真的。

请记住：完成任务不止一种方法。您能想到其他方法来应对原始挑战吗？欢迎评论！