Enable auto commit in SQLPLUS or batch file of Oracle

Auto commit in SQLPLUS windows or batch file of Oracle

Check the autocommit setting in SQLPLUS

SQL> show autocommit
autocommit OFF

Enable the autocommit property in SQLPLUS

SQL> set autocommit on
SQL> show autocommit
autocommit IMMEDIATE

Enable every 10 DML statement its commit

SQL> set autocommit 10
SQL> show autocommit
AUTOCOMMIT ON for every 10 DML statements

Disable the autocommit property

SQL> set autocommit off
SQL>
SQL> show autocommit
autocommit OFF

Example of using enable every 2 DML statement it should commit
You set the autommit to 2 then it automatically fire commit after 2 statement execution in sqlplus windows:

SQL> show autocommit
AUTOCOMMIT ON for every 10 DML statements

SQL> set autocommit 2

SQL> show autocommit
AUTOCOMMIT ON for every 2 DML statements

SQL> desc test

Name            Null?    Type
--------------- -------- --------------
ID                       NUMBER

SQL> insert into test values (1);

1 row created.

SQL> insert into test values(2);

1 row created.

Commit complete.
SQL> insert into test values(3);

1 row created.

SQL> insert into test values(4);

1 row created.

Commit complete.
SQL>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.