Oracle sqlplus参数autocommit（自动提交）的设置

原创

小麦苗123 2021-04-13 11:36:57 ©著作权

文章标签 Oracle sqlplus autocommit 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

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;

官网：

https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#SQPUG060

http://blog.itpub.net/26736162/viewspace-2121072/

需要注意的是，无论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

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;

----------

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

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;

----------

连续在当前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

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);

1 row 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.