自主事务中的Oracle DDL

Question

我需要在Oracle数据库上执行一堆（最多~1000000）SQL语句。这些语句应该在结束时产生引用一致的状态，并且如果出现错误，则应回滚所有语句。这些语句不按引用顺序排列。因此，如果启用了外键约束，则其中一个语句可能会导致外键违规，即使稍后执行的语句将修复此违规也是如此。

我尝试先禁用外键，然后在执行所有语句后再启用它们。我认为当实际发生外键违规时，我将能够回滚。但是我错了，我发现Oracle中的每个DDL语句都以提交开始，因此无法以这种方式回滚语句。以下是我的禁用外键脚本：

begin 
  for i in (select constraint_name, table_name from user_constraints
            where constraint_type ='R' and status = 'ENABLED') 
    LOOP execute immediate 'alter table '||i.table_name||' disable constraint 
                           '||i.constraint_name||''; 
  end loop;
end;

经过一些研究，我发现建议执行DDL语句（例如此案例）时应在自主事务中执行。因此，我尝试在自主事务中运行DDL语句。这导致了以下错误：

ORA-00054：资源忙，指定NOWAIT获取

我猜测这是因为主事务仍然对表具有DDL锁定。

我在这里做错了什么，还是有其他方法使这种情况起作用？

- swamplord

Answer 1

有几种潜在的方法。

首先要考虑的是，无论你在表级别上做什么，都将应用于使用该表的所有会话。如果你没有独占该表的访问权限，你可能不想删除/重新创建约束或禁用/启用它们。

其次要考虑的是，你可能不想处于回滚一百万次插入/更新的位置。回滚可能会很慢。

通常我会加载到临时表中。然后从临时表中进行单个插入到目标表中。作为一个单独的语句，Oracle将在最后应用所有的检查约束。

如果你不能通过临时表（例如对现有数据的更新），在开始之前将约束deferrable initially immediate。然后，在你的会话中，

SET CONSTRAINTS emp_job_nn, emp_salary_min DEFERRED;

您可以应用更改，当您提交时，将验证约束。

您应熟悉DML错误日志记录，因为它可以帮助识别导致违规的任何行。

- Gary Myers

---

我刚刚发现了“SET CONSTRAINTS”技巧，然后来到这里回答我的问题，看到了你的答案：）。谢谢。 - swamplord