Autocommit (Part 1)

(Tested on an Oracle 9 database.)

This is a worked example to show how the SQL*Plus autocommit command works. First show its default value:

SQL> show autocommit
autocommit OFF
SQL>

Show that when autocommit is OFF, transactions are not committed automatically:

SQL> create table test_autocommit (col1 varchar2(10))
  2  /

Table created.

SQL> insert into test_autocommit values('Andrew')
  2  /

1 row created.

SQL> select * from test_autocommit
  2  /

COL1
----------
Andrew

SQL> rollback
  2  /

Rollback complete.

SQL> select * from test_autocommit
  2  /

no rows selected

SQL>

Show that when autocommit is ON, transactions are committed automatically (the commit statement you can see was done by Oracle, not by me):

SQL> set autocommit on
SQL> show autocommit
autocommit IMMEDIATE
SQL> insert into test_autocommit values('Andrew')
  2  /

1 row created.

Commit complete.
SQL> select * from test_autocommit
  2  /

COL1
----------
Andrew

SQL> rollback
  2  /

Rollback complete.

SQL> select * from test_autocommit
  2  /

COL1
----------
Andrew

SQL>

If autocommit is set to 3 (for example) then Oracle commits automatically after 3 insert, update or delete statements (again, Oracle did the commit you can see):

SQL> set autocommit 3
SQL> show autocommit
AUTOCOMMIT ON for every 3 DML statements
SQL> update test_autocommit set col1 = 'Brian'
  2  /

1 row updated.

SQL> select * from test_autocommit
  2  /

COL1
----------
Brian

SQL> insert into test_autocommit values ('Colin')
  2  /

1 row created.

SQL> select * from test_autocommit
  2  /

COL1
----------
Brian
Colin

SQL> delete from test_autocommit where col1 = 'Brian'
  2  /

1 row deleted.

Commit complete.
SQL> select * from test_autocommit
  2  /

COL1
----------
Colin

SQL> rollback
  2  /

Rollback complete.

SQL> select * from test_autocommit
  2  /

COL1
----------
Colin

SQL>