在事务中包装Oracle模式更新 [英] Wrap an Oracle schema update in a transaction

问题描述

我有一个程序会定期更新其数据库架构.有时，其中一个DDL语句可能会失败，如果失败了，我想回滚所有更改.我将更新打包在这样的事务中:

I've got a program that periodically updates its database schema. Sometimes, one of the DDL statements might fail and if it does, I want to roll back all the changes. I wrap the update in a transaction like so:

BEGIN TRAN;

CREATE TABLE A (PKey int NOT NULL IDENTITY, NewFieldKey int NULL, CONSTRAINT PK_A PRIMARY KEY (PKey));
CREATE INDEX A_2 ON A (NewFieldKey);
CREATE TABLE B (PKey int NOT NULL IDENTITY, CONSTRAINT PK_B PRIMARY KEY (PKey));
ALTER TABLE A ADD CONSTRAINT FK_B_A FOREIGN KEY (NewFieldKey) REFERENCES B (PKey);

COMMIT TRAN;

在执行过程中，如果其中一条语句失败，我将执行ROLLBACK而不是COMMIT.这在SQL Server上效果很好，但对Oracle却没有理想的效果. Oracle似乎在每个DDL语句之后执行一个隐式COMMIT:

As we're executing, if one of the statements fail, I do a ROLLBACK instead of a COMMIT. This works great on SQL Server, but doesn't have the desired effect on Oracle. Oracle seems to do an implicit COMMIT after each DDL statement:

• http://www.orafaq.com/wiki/SQL_FAQ#What_are_the_Derence_between .2C_DML_and_DCL_commands.3F
• http://infolab.stanford.edu/〜ullman/fcdb/oracle/or-nonstandard.html#transactions

• http://www.orafaq.com/wiki/SQL_FAQ#What_are_the_difference_between_DDL.2C_DML_and_DCL_commands.3F
• http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html#transactions

有没有任何方法来关闭此隐式提交?

Is there any way to turn off this implicit commit?

推荐答案

您无法将其关闭.通过设计脚本以在表已经存在的情况下删除表，可以很容易地解决该问题……

You can not turn this off. Fairly easy to work around by designing your scripts to drop tables in the event they already exist etc...

您可以看看使用FLASHBACK数据库，我相信您可以在模式/对象级别执行此操作，但是请检查文档以确认这一点.您需要使用10G才能正常工作.

You can look at using FLASHBACK database, I believe you can do this at the schema/object level but check the docs to confirm that. You would need to be on 10G for that to work.

这篇关于在事务中包装Oracle模式更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！