22

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

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
FoxyBOA
  • 5,788
  • 8
  • 48
  • 82
  • 2
    possible duplicate of [Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?](http://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql) – Dave Costa Jan 17 '11 at 13:31

3 Answers3

25

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

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

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
21

No. In Oracle DDL statements themselves are not transactional, or rather, each DDL statement includes an implicit commit before and after the statement.

Any data modifications that are part of that statement are done within that transaction. For example, certain alter table statements may fire triggers on the table, which in turn can do other DML. All of that will run in the transaction that embeds the DDL statement, and if the DDL statement would fail or is cancelled, all of it will be rolled back.

But you can't start an explicit transaction to roll back multiple DDL statements (yet).

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 DDL statements and information about their behaviour regarding locks and transactions.

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

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
4

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.

ddevienne
  • 1,802
  • 2
  • 17
  • 28
  • Please, share your investigation. Quickly review the link. Could works only with create table/view. WHat about other DML? – FoxyBOA Aug 30 '12 at 10:52
  • 2
    No DML - `CREATE SCHEMA AUTHORIZATION` only applies to a series of tables, views, as well as grants on those objects. – Jeffrey Kemp Aug 31 '12 at 05:57