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.
When AUTOCOMMIT IMMEDIATE is activated all the DML
statements executed afterward will be treated as a transaction and will be committed
to the Database.
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.
ReplyDeletenice example for autocommit;
ReplyDeletefollowing 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.
ReplyDeleteIt 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.
Whenever sqlerror then exit 1 rollback
ReplyDeleteWhenever oserror then exit 1 rollback