Oracle sqlplus引數autocommit（自動提交）的設定

lhrbest發表於2017-12-31
OracleSQLMIT

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

 

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

 

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筆試面寶典》讀者群       小麥苗的微店

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

Oracle sqlplus引數autocommit（自動提交）的設定
 DBA筆試面試講解群
 《DBA寶典》讀者群 歡迎與我聯絡



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2149538/，如需轉載，請註明出處，否則將追究法律責任。

相關文章