Friday, December 17, 2010

DDL Causes Implied Commit

If you add, modify or remove table data, you can use rollback to reverse that change if you have not done a commit to save it. To demonstrate this I created a table and showed that it was empty:

SQL> create table andrew
  2  (one_column number)
  3  /

Table created.


SQL> select * from andrew
  2  /

no rows selected

SQL>


Then I added a row to the table and displayed it afterwards:

SQL> insert into andrew values (1)
  2  /

1 row created.

SQL> select one_column from andrew
  2  /

ONE_COLUMN
----------
         1

SQL>


I used rollback to reverse the transaction and showed that the table was empty again:

SQL> rollback
  2  /

Rollback complete.

SQL> select * from andrew
  2  /

no rows selected

SQL>


I repeated the process but did a commit to save the change before doing the rollback:

SQL> insert into andrew select 2 from dual
  2  /

1 row created.

SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         2

SQL> commit
  2  /

Commit complete.

SQL> rollback
  2  /

Rollback complete.

SQL>


This time the row was still in the table:

SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         2

SQL>


Finally, I multiplied the value in the row by 2 but did not commit the change:

SQL> update andrew set one_column = one_column * 2
  2  /

1 row updated.

SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         4

SQL>


I ran a data definition language (DDL) statement. This caused an implied commit:

SQL> create public synonym andrew for andrew
  2  /

Synonym created.

SQL>


So when I tried to rollback the change this had no effect and the value stayed the same:

SQL> rollback
  2  /

Rollback complete.


SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         4

SQL>

No comments: