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

最新推荐文章于 2024-08-20 19:34:32 发布

六六子大顺1

最新推荐文章于 2024-08-20 19:34:32 发布

阅读量4.8k

点赞数

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.

See Also:

SET EXITC[OMMIT] {ON | OFF}

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 SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n} for more information.

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2018-01-01 06:00 ~ 2018-01-31 24:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

● 好消息：小麦苗OCP、OCM开班啦，详情请点击：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群小麦苗的微店

.............................................................................................................................................