Re: Oracle does not rollback correctly after ENABLE CONSTRAINT failed

From: John K. Hinsdale <hin_at_alma.com>
Date: 5 Feb 2007 11:46:13 -0800
Message-ID: <1170704773.309848.174860@a34g2000cwb.googlegroups.com>

> 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 -=-=-=-==- Received on Mon Feb 05 2007 - 13:46:13 CST