将 Oracle 模式更新包装在事务中
我有一个程序可以定期更新其数据库模式。 有时，其中一个 DDL 语句可能会失败，如果失败，我想回滚所有更改。 我将更新包装在一个事务中，如下所示：
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：
- 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
有任何方法可以关闭此隐式提交吗？
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;
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_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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您无法将其关闭。 通过设计脚本来在表已经存在的情况下删除表等，相当容易解决这个问题...
您可以查看使用FLASHBACK数据库，我相信您可以在模式/对象级别执行此操作，但请检查文档以确认这一点。 您需要使用 10G 才能正常工作。
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...
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.