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

On Nov 11, 7:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
> zigzag..._at_yahoo.com 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.http://www.psoug.org/reference/schema.html
>
> Or perhaps you should look at the DBMS_REDEFINITION built-in package.http://www.psoug.org/reference/dbms_redefinition.html
> which has been around since 9.0.1.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

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