oracle如何实现自动提交,Oracle sqlplus参数autocommit（自动提交）的设置

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在Oracle数据库中，在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。因为Oracle数据库的默认事务隔离级别是提交读(Read

Committed)。提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

(1)显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT;

(2)隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3)自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON;

官网：

需要注意的是，无论AUTOCOMMIT设置为何值，当退出SQL*Plus时，当前会话所有的DML操作所改变的数据都会被提交。

SYS@PROD1> set auto

SP2-0281: autocommit missing set option

Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }

SYS@PROD1> show auto

autocommit OFF

SCOTT@PROD1>  set autocommit

on

SCOTT@PROD1> show autocommit

autocommit

IMMEDIATE

OFF为默认值，表示关闭自动提交；ON和IMM都表示打开自动提交，二者没有区别；n表示成功执行n条DML操作后再自动提交。n不能小于0，也不能大于20亿(2,000,000,000)。注意，这里不是一个DML语句所影响的行数，而是DML语句的个数。

实验过程如下：

1、数据库版本

SQL> select * from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release

11.2.0.1.0 - 64bit Production

2、查看sqlplus的控制是否自动提交的参数autocommit。无论AUTOCOMMIT设置为何值，当退出SQL*Plus时，当前会话所有的DML操作所改变的数据都会被提交。

SQL> show user

USER is "SCOTT"

Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] |

n }

SQL>

SQL> show autocommit

autocommit OFF

3、产生DML不提交

SQL> create table t_commit_lhr(a number);

SQL> insert into t_commit_lhr values(1);

1 row created.

退出当前会话

Disconnected from Oracle Database 11g Enterprise

Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application

Testing options

4，若中途退出未提交DML的SQLPLUS会话，则自动提交DML事务

[oracle@seconary ~]$ sqlplus scott/system

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct

17 21:45:13 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release

11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real

Application Testing options

SQL> select * from t_commit_lhr;

A

----------

1

5，调整自动提交为提交，可见autocommit为on，即不用显式再发起命令commit

SQL> show autocommit

autocommit OFF

SQL>

set autocommit on

SQL>

show autocommit

autocommit

IMMEDIATE

SQL> select * from t_commit_lhr;

no rows selected

SQL> insert into t_commit_lhr values(2);

1 row created.

Commit

complete.

SQL> host

[oracle@seconary ~]$ sqlplus scott/system

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct

17 21:53:55 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release

11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real

Application Testing options

SQL> select * from t_commit_lhr;

A

----------

1

2

连续在当前SQLPLUS会话中运行5个DML语句后会自动提交

SQL> set autocommit 5

SQL> show autocommit

AUTOCOMMIT ON for every 5 DML statements

[oracle@seconary ~]$ sqlplus scott/system

SQL> truncate table t_commit_lhr;

Table truncated.

SQL> select * from t_commit_lhr;

no rows selected

SQL> insert into t_commit_lhr select level from

dual connect by level<=6;

6 rows created.

SQL> host

[oracle@seconary ~]$ sqlplus scott/system

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct

17 21:58:41 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release

11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real

Application Testing options

SQL> select * from t_commit_lhr;

no rows selected

SQL> insert into t_commit_lhr values(1);

1 row created.

SQL> insert into t_commit_lhr values(1);

1 row created.

SQL> insert into t_commit_lhr values(1);

1 row created.

SQL> insert into t_commit_lhr values(1);

1 row created.

SQL> insert into t_commit_lhr values(1);

1row created.

Commit

complete.

SQL>

第5条insert执行完毕后出现Commit complete.提示。

&说明：

有关Oracle AUTOCOMMIT(自动提交)的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2149538/

SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}

Controls when Oracle Database commits pending changes to the

database after SQL or PL/SQL commands.

ON commits pending changes to the database after Oracle Database executes

each successful INSERT, UPDATE, or DELETE, or PL/SQL block. OFF suppresses

automatic committing so that you must commit changes manually (for example, with

the SQL command COMMIT). IMMEDIATE functions in the same manner as ON. n commits pending changes to the database after Oracle

Database executes n successful SQL INSERT, UPDATE, or

DELETE commands, or PL/SQL blocks. n cannot be less

than zero or greater than 2,000,000,000. The statement counter is reset to zero

after successful completion of n INSERT, UPDATE or

DELETE commands or PL/SQL blocks, a commit, a rollback, or a SET AUTOCOMMIT

command.

SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any

uncommitted data is committed by default.

Note:

For this feature, a PL/SQL block is considered one

transaction, regardless of the actual number of SQL commands contained within

it.

Saving Changes to the Database Automatically

You can specify changes you wish to make to the information stored in the

database using the SQL Database Manipulation Language (DML) commands UPDATE,

INSERT, and DELETE—which can be used independently or within a PL/SQL block.

These changes are not made permanent until you enter a SQL COMMIT command or a SQL Database Control Language

(DCL) or Database Definition Language (DDL) command (such as CREATE TABLE), or

use the autocommit feature. The SQL*Plus autocommit feature causes pending

changes to be committed after a specified number of successful SQL DML

transactions. (A SQL DML transaction is either an UPDATE, INSERT, or DELETE

command, or a PL/SQL block.)

You control the autocommit feature with the SQL*Plus AUTOCOMMIT system variable. Regardless of the AUTOCOMMIT

setting, changes are committed when you exit SQL*Plus successfully.

See Also:

Example 4-5 Turning

Autocommit On

To turn the autocommit feature on, enter

SET AUTOCOMMIT ON

Alternatively, you can enter the following to turn the autocommit feature

on:

SET AUTOCOMMIT IMMEDIATE

Until you change the setting of AUTOCOMMIT, SQL*Plus automatically commits

changes from each SQL DML command that specifies changes to the database. After

each autocommit, SQL*Plus displays the following message:

COMMIT COMPLETE

When the autocommit feature is turned on, you cannot roll back changes to the

database.

To commit changes to the database after a number of SQL DML commands, for

example, 10, enter

SET AUTOCOMMIT 10

SQL*Plus counts SQL DML commands as they are executed and commits the changes

after each 10th SQL DML command.

Note:

For this feature, a PL/SQL block is regarded as one

transaction, regardless of the actual number of SQL commands contained within

it.

To turn the autocommit feature off again, enter the following command:

SET AUTOCOMMIT OFF

To confirm that AUTOCOMMIT is now set to OFF, enter the following SHOW

command:

SHOW AUTOCOMMIT

AUTOCOMMIT OFF

See for

more information.