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

From: Serge Rielau <>
Date: Tue, 11 Nov 2008 06:34:27 -0500
Message-ID: <>

Noons wrote:
> On Nov 10, 11:06 pm, Serge Rielau <> wrote:

>> That's the first I hear about that. Can you point to any docs explaining
>> the behavior you imply? I'm also told that SQL Server (like DB2) has
>> transactional DDL. Even TRUNCATE table is transactional.

> Instead of reading marketing materials, try using the product.
Unfortunately my job forbids me to actually use other DBMS's (lawyers...). So I'm stuck with asking questions. :-)

> It is supposed to *be* there. However, it is so inconsistent as to
> make it essentially unusable.
> For example: Try to drop a table that is not there - a common example
> of a
> "oops" statement in an upgrade script. It produces an error, but does
> cause a rollback to take place or lock other scripts out.
Why would is cause a rollback? If you are doing DML (let's move to safer ground) and you are getting an error - say a check constraint violation - do you expect teh whole transaction to roll back? Typically I'd expect the statement to fail (statement atomicity) and then an exception handler can decide whether the problem as serious enough to fail the transaction or recover from it somehow.
That's what those WHENEVER exceptions are there fore.

> Now, try to select form an incorrect table name - another common
> example
> os a "oops" statement in an u[grade script. It produces an error, and
> put the transaction in a suspended state that is neither rollback nor
> commit:
> if you are running the script interactively, you then have to manually
> take a
> decision on which way it goes.

Interesting. In my world again the statement would fail (and roll back) but the transaction remains intact. An exception handler initiates rollback if desired.


Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Received on Tue Nov 11 2008 - 05:34:27 CST

Original text of this message