Thursday, 12 December 2013

AUTOCOMMIT in SQL- Simple and Useful

DML Statements or Data Manipulation Language statements will not be committed unless they are committed explicitly, But if you use any data definition language (DDL) statement before or after , then Oracle Database issues an implicit COMMIT.

There is a provision in Oracle to commit every DML Transaction automatically once its run. This is called AUTOCOMMIT. We can also turn AUTOCOMMIT on and off based on our requirement.

Below are commands to check for AUTOCOMMIT.

When AUTOCOMMIT IMMEDIATE is activated all the DML statements executed afterward will be treated as a transaction and will be committed to the Database.

We can also limit the number of AUTOCOMMIT to number that we want using the below command.


  1. Autocommit is a option of the SQL*Plus, so it can be turned on in that tool only. The article does not say anything about it.

  2. nice example for autocommit;

  3. following on from Cepren - sorry for the spelling - it is important to know that "autocommit" is a tool feature and not an Oracle feature - although I believe it is a feature of MySQL.
    It is a quite dangerous feature and one that I always disable.
    When doing data corrections I will run a series of inserts/updates/deletes with query checks in between so that at the end I can commit the whole transaction set knowing that it is all correct.
    If I am unhappy about anything I can rollback everything and start again.
    In the meantime other users have no knowledge of these actions until I have finally committed.
    I would argue that commit should always be a positively decided action. If you are in a busy rushed environment then with autocommit on mistakes can easily happen.
    With it off you have more chance to think before commit.

  4. Whenever sqlerror then exit 1 rollback
    Whenever oserror then exit 1 rollback


Related Posts Plugin for WordPress, Blogger...