Re: Why Oracle does not allow rollback of DDL statements?

From: <>
Date: Tue, 11 Nov 2008 18:46:05 -0800 (PST)
Message-ID: <>

On Nov 11, 7:10 pm, DA Morgan <> wrote:
> wrote:
> > For examples, I am developing installation script to create schema
> > changes
> > for new release of system.  If there was transaction DDL, I can write
> > all the changes and in case of error, roll them back. In absence of
> > transactional DDL, I have to write the script: if a schema change was
> > already made do not make it, otherwise make the change.
> Your premise is faulty and, it appears, you are trying to reinvent the
> wheel. Use CREATE SCHEMA and if it all doesn't succeed it all is rolled
> back.
> Or perhaps you should look at the DBMS_REDEFINITION built-in package.
> which has been around since 9.0.1.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> (replace x with u to respond)
> Puget Sound Oracle Users

Create schema works only for create table, create views and grants. It won't let you do alter statements, create triggers… Dbms_redef appears only for redefining tables. Its purpose is on-line redefinition of tables. It has too much overhead: requires lots of additional storage depending on size of your tables.

Both methods do not provide all changes, which are done in schema upgrade scripts. Work around which I have been using in absence of transaction ddl is much better.

I doubt Oracle will provide transaction based ddl because of its architecture and all exiting base of code; however, this does not mean that this feature is not useful.

As I pointed databases are the only thing I know of which provide transactions. Sure it will be nice if transaction is not limited to DML. Received on Tue Nov 11 2008 - 20:46:05 CST

Original text of this message