oracle11g sqlplus引數autocommit

結論

  1，資料庫版本為ORACLE11G
  2，控制是否提交的引數為sqlplus的autocommit
  3, 引數預設為自動提交，即使中途退出了sqlplus會話，仍會自動提交DML事務
  4, 調整autocommit引數只會在當前SQLPLUS會話生效
  5, 檢視引數命令為show autocommit
  6, 修改引數命令為set autocommit
  7, autocommit引數預設值為false
  8, set autocommit x,表示在當前會話連續執行X個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
SQL> show user
USER is "SCOTT"


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


3,產生DML不提交
SQL> insert into t_commit 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;


         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;


no rows selected


SQL> insert into t_commit values(1);


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;


         A
----------
         1


連續在當前SQLPLUS會話中執行20個DML語句後會自動提交
SQL> set autocommit 20
SQL> show autocommit
AUTOCOMMIT ON for every 20 DML statements


[oracle@seconary ~]$ sqlplus scott/system
SQL> select * from t_commit;


         A
----------
         1      




SQL> insert into t_commit select level from dual connect by level<=21;


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


         A
----------
         1




SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


SQL> insert into t_commit values(1);


1 row created.


Commit complete.
SQL>