DDL Causes Implied Commit

If you do an insert, update or delete, you can reverse that change if it has not been committed:

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

Table created.

SQL>

The SQL above has created a table. Show that it starts off empty:

SQL> select * from andrew
  2  /

no rows selected

SQL>

Now insert a row into the table:

SQL> insert into andrew values (1)
  2  /

1 row created.

SQL>

Display the row you have just added:

SQL> select one_column from andrew
  2  /

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

SQL>

Rollback the transaction and show that the table is empty again:
 
SQL> rollback
  2  /

Rollback complete.

SQL> select * from andrew
  2  /
 
no rows selected

SQL>

Repeat the process but commit the insert 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 should still be in the table:

SQL> select * from andrew
  2  /

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

SQL>

Finally, multiply the value in the row by 2:

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

1 row updated.

SQL> select * from andrew
  2  /

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

SQL>

Run a data definition language (DDL) statement. This causes an implied commit:

SQL> create public synonym andrew for andrew
  2  /

Synonym created.

SQL>

Try to rollback the update (this will have no effect):

SQL> rollback
  2  /

Rollback complete.
 
SQL>

Show that the value in the row is still 4:
 
SQL> select * from andrew
  2  /

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

SQL>