Oracle: DDL and transaction rollback

问题:

Could in Oracle DDL (create/alter) be transactional like they are in MS SQL (started from 2005)?

回答1:

No. In Oracle DDL statements themselves are not transactional.

Running a DDL statement will implicitly commit any open transaction for that session before starting the actual work.

In addition some statements, like an alter table statement, may fail if another session has an open transaction on the object being modified or one of its dependencies. You can set a ddl_lock_timeout to specify how long you want Oracle to wait for the object to become available.

See DDL Statements for a summary of types of DLL statements and information about their behaviour regarding locks and transactions.

回答2:

DDL is not transactional in Oracle. From the 11.2 doc:

Oracle Database implicitly commits the current transaction before and after every DDL statement.

回答3:

Just discovered this, so needs more investigation, but Oracle's create schema authorization at least allows to bundle several DDL statements in a single transaction, thus avoiding several commits, and guarantees no side-effects, i.e. a rollback, if any one of the DDL statement fails.

It is unclear whether the rollback applies to the wrapped DDL statements only, as-if the create schema started a save-point, or the outer transaction with possibly some prior DML statement.

You still cannot avoid the commit after the last wrapped DDL statement complete successfully though.