Re: Oracle does not rollback correctly after ENABLE CONSTRAINT failed

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 05 Feb 2007 21:56:44 +0100
Message-ID: <45C79A0C.4030706@gmail.com>

John K. Hinsdale schrieb:

>> On 5 feb, 16:52, uhaham_at_gmail.com wrote:
>>> After a failed attempt to enable foreign key the rollback did not
>>> return to the original database state.

>
> On Feb 5, 11:49 am, "Carlos" <miotromailcarlos_at_netscape.net> wrote:

>> DDL always do an implicit COMMIT. It is done BEFORE the DDL. So, it
>> doesn't matter if the DDL succeeds or fails: the commit is done (no
>> chance for a rollback then).

>
> My observation is otherwise. This is probably a policy of the client
> software that establishes and maintains the connection to
> Oracle. For SQL*Plus, it does seem to keep the transaction
> alive (uncommitted) and available for rollback, at least for an
> intervening, failed CREATE TABLE statement. See transcript
> below for client and server V. 10.2.0.1.0. One well know behavior
> SQL*Plus is that it WILL commit in-progress changes on exit.
>
> Some client drivers have an "autocommit" mode, enabled by default,
> which commits each statement as it is issued, to simplify use.
> uhaham, what client software are you using?
>
> Transcript of SQL*Plus rollback after failed DDL follows.
>
> HTH,
> John Hinsdale
>
> -=-=-=-=-=- Transcript -=-=-=-==-
> SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 5 14:35:15 2007
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
> Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> create table foo ( n number );
>
> Table created.
>
> SQL> select * from foo;
>
> no rows selected
>
> SQL> insert into foo values ( 10 );
>
> 1 row created.
>
> SQL> select * from foo;
>
> N
> ----------
> 10
>
> SQL> create table bar ( );
> create table bar ( )
> *
> ERROR at line 1:
> ORA-00904: : invalid identifier
>
>
> SQL> select * from foo;
>
> N
> ----------
> 10
>
> SQL> rollback;
>
> Rollback complete.
>
> SQL> select * from foo;
>
> no rows selected
>
> SQL> exit
> Disconnected from Oracle Database 10g Enterprise Edition Release
> 10.2.0.1.0 - 64bit Production
> With the Partitioning, OLAP and Data Mining options
>
> -=-=-=-=-=- Transcript -=-=-=-==-
>

You are surely aware, that, in opposite to Carlos example, yours will get a parse error ( you can easily see it in the 10046 trace) and so never will be executed, so no impact on previous transaction. A valid DDL statement (like alter table add constraint in previous example) will be executed , error occurs within recursive sql and statement itself fails, but transaction is commited exactly due to reason mentioned by Carlos.

Here a variation of your example

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> create table foo ( n number );

Table created.

SQL> select * from foo;

no rows selected

SQL> insert into foo values ( 10 );

1 row created.

SQL> select * from foo;

          N

---

         10

SQL> create table bar ( );
create table bar ( )

                    *

ERROR at line 1:
ORA-00904: : invalid identifier

SQL> select * from foo;

          N

---

         10

SQL> rollback;

Rollback complete.

SQL> insert into foo values(1);

1 row created.

SQL> alter table foo add constraint foo_c check (n>10); alter table foo add constraint foo_c check (n>10)

                                *

ERROR at line 1:
ORA-02293: cannot validate (SCOTT.FOO_C) - check constraint violated

SQL> rollback;

Rollback complete.

SQL> select * from foo;

          N

---

          1

SQL> alter session set events '10046 trace name context off';

Best regards

Maxim Received on Mon Feb 05 2007 - 14:56:44 CST