自治事务中的Oracle DDL
我需要在 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 锁定。
我在这里做错了什么，还是有其他方法可以让这个场景发挥作用？
I need to execute a bunch of (up to ~1000000) sql statements on an Oracle database. These statements should result in a referentially consistent state at the end, and all the statements should be rolled back if an error occurs. These statements do not come in a referential order. So if foreign key constraints are enabled, one of the statements may cause a foreign key violation even though, this violation would be fixed with a statement that would be executed later on.
I tried disabling foreign keys first and enabling them after all statements were executed. I thought I would be able to roll back when there was an actual foreign key violation. I was wrong though, I found out that every DDL statement in Oracle started with a commit, so there was no way to rollback the statements this way. Here is my script for disabling foreign keys:
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;
After some research, I found out that it was recommended to execute DDL statements, like in this case, in an autonomous transaction. So I tried to run DDL statements in an autonomous transaction. This resulted in the following error:
ORA-00054: resource busy and acquire with NOWAIT specified
I am guessing this is because the main transaction still has DDL lock on the tables.
Am I doing something wrong here, or is there any other way to make this scenario work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有几种潜在的方法。
首先要考虑的是，您在表级别所做的任何操作都将应用于使用该表的所有会话。如果您没有对该表的独占访问权限，您可能不想删除/重新创建约束，或禁用/启用它们。
要考虑的第二件事是您可能不希望回滚一百万次插入/更新。回滚可能会很慢。
通常我会加载到临时表中。然后从临时表到目标表执行一次 INSERT。作为单个语句，Oracle 将在最后应用所有检查约束。
如果您无法使用临时表（例如更新现有数据），请在开始之前制定约束 最初可延迟立即。然后，在您的会话中，
您可以应用更改，并且在提交时，约束将得到验证。
您应该熟悉 DML 错误日志记录，因为它可以帮助识别任何错误导致违规的行。
There's several potential approaches.
The first thing to consider is that whatever you do at the table level will apply to all sessions using that table. If you haven't got exclusive access to that table, you probably don't want to drop/recreate constraints, or disable/enable them.
The second thing to consider is that you probably don't want to be in a position of rolling back a million inserts/updates. Rolling back can be SLOW.
Generally I would load into a temporary table. Then do a single INSERT from the temporary table into the destination table. As a single statement, Oracle will apply all the check constraints at the end.
If you can't go through a temporary table (eg updates to existing data), before starting make the constraints deferrable initially immediate. Then, within your session,
You can then apply the changes and, when you commit, the constraints will be validated.
You should aquaint yourself with DML error logging as it can help identify any rows causing violations.