To COMMIT means to make changes to data permanent. Before changes are stored in the database both the old and new data exist. The new data is kept in a buffer until you issue the COMMIT statement. When a COMMIT is issued the changes are made permanent. If it is necessary to return the data to its prior state, you can do so only until a COMMIT is performed.

To undo work and return the tables to their prior state you use the ROLLBACK command. Rollback will undo any work that has not been committed. A rollback will also be performed if the system or database goes down while you are working in it.

There are however some actions which will force a COMMIT to occur even without your specific instructions. If you need to use any of the following commands QUIT, EXIT, CREATE TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, GRANT, REVOKE or ALTER they will act just as if you had issued the COMMIT command.

To commit your work, simply type COMMIT as illustrated below.

You will receive a Commit complete message.

For rollbacks, type ROLLBACK:

You will receive a Rollback complete message.

SQL*Plus also has a facility to automatically commit work without the COMMIT statement. This is controlled by autocommit. To see if COMMIT is set to automatic, type:

You will probably receive a message that AUTOCOMMIT is Off. Off is the default setting. This means that your inserts, updates and deletes will NOT be final until you commit them. To change the default, type:

Verify that AUTOCOMMIT is now on by typing:

You will receive this message: autocommit IMMEDIATE

The ON or IMMEDIATE parameter can be used to set autocommit on. The message that will return will always be 'autocommit IMMEDIATE'. When you exit SQL*Plus, AUTOCOMMIT will return to the default setting. Therefore you will have to reset it each time you work in SQL*Plus if you want your work committed immediately.

To experiment with the use of the buffer and rollback set autocommit back to the default:

SQL> SET AUTOCOMMIT OFF

No message is returned so lets check it.

SQL> SHOW AUTOCOMMIT

autocommit OFF

Now to update the EMPLOYEE table while the autocommit is off:

You will receive a message from ORACLE that tells you how many rows were updated.

Select all rows from EMPLOYEE to view the change:

SQL> SELECT * FROM EMPLOYEE;

When we query from the table we see the updated information. Therefore we are able to view what effect the changes will have on the table if we commit them.

To rollback the changes in the buffer area we issue the rollback statement:

SQL> ROLLBACK;

A Rollback complete is returned.

Again, we may select all rows from the table to prove the rollback did in fact restore our data to its prior state:

SQL> SELECT * FROM EMPLOYEE;

The table is returned to its original state.

Last revised 10/30/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems