ITPub博客

【SQL*Plus】使用Oracle 11gR2的EXITCOMMIT参数控制在SQL*Plus中exit时commit的行为

原创 Linux操作系统 作者:secooler 时间:2012-03-14 23:27:17 0 删除 编辑
  偶然间邂逅了这个在Oracle 11gR2版本中新增加的SQL*Plus参数“EXITCOMMIT”,该参数作用是精确控制exit的时候commit的行为。

1.Oracle官方文档关于这个新增加的参数的描述
  先看一下Oracle 11gR2官方文档中关于这个参数的描述,非常的清晰,这个“EXITCOMMIT”参数与“AUTOCOMMIT”参数共同决定了exit时会话的行为,是提交(commit)还是回滚(rollbak)。

SET EXITC[OMMIT] {ON | OFF}
Specifies whether the default EXIT behavior. is COMMIT or ROLLBACK.
The default setting is ON, which means that work is committed on exit, whether you expected it to be committed or not. Set EXITCOMMIT OFF to rollback work on exit.
Table 12-5 shows the exit action associated with combinations of SET commands (AUTOCOMMIT & EXITCOMMIT) and the EXIT command.
Table 12-5 Exit Behavior. AUTOCOMMIT, EXITCOMMIT, EXIT
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
ON          ON          -         COMMIT
ON          OFF         -         COMMIT
OFF         ON          -         COMMIT
OFF         OFF         -         ROLLBACK
ON          ON          COMMIT    COMMIT
ON          ON          ROLLBACK  COMMIT
ON          OFF         COMMIT    COMMIT
ON          OFF         ROLLBACK  COMMIT
OFF         ON          COMMIT    COMMIT
OFF         ON          ROLLBACK  ROLLBACK
OFF         OFF         COMMIT    COMMIT
OFF         OFF         ROLLBACK  ROLLBACK

2.系统默认设置行为的实验确认
  在不做任何干预的默认情况下,AUTOCOMMIT是“OFF”,EXITCOMMIT是“ON”。实际确认一下。
[oracle@secDB /]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 21:56:02 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sys@ora11g> show AUTOCOMMIT
autocommit OFF
sys@ora11g> show EXITCOMMIT
exitcommit ON

3.我这里为了演示方便在普通用户sec下创建一个测试用表t
sys@ora11g> conn sec/sec
Connected.
sec@ora11g> create table t (x number);

Table created.

4.先来讨论在默认情况下exit与commit的行为
  当AUTOCOMMIT为“OFF”,EXITCOMMIT是“ON”,根据文档中的描述应该有如下三种行为
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
OFF         ON          -         COMMIT
OFF         ON          COMMIT    COMMIT
OFF         ON          ROLLBACK  ROLLBACK

1)第一种行为实验:AUTOCOMMIT为OFF,EXITCOMMIT为ON,直接退出SQL*Plus
sec@ora11g> insert into t values (1);

1 row created.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:13:54 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         1

  结论得证,直接退出SQL*Plus之后,之前插入的数据被提交(commit)了。

2)第二种行为:AUTOCOMMIT为OFF,EXITCOMMIT为ON,先输入commit再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> insert into t values (2);

1 row created.

sec@ora11g> commit;

Commit complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:16:30 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         2

  结论得证,结果是提交(commit)了。这个结论很显然,认真观察一下规律就知道,一切通过显式的使用了commit命令,数据修改动作一定是被确认提交的。

3)第三种行为:AUTOCOMMIT为OFF,EXITCOMMIT为ON,先输入rollback再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> insert into t values (3);

1 row created.

sec@ora11g> rollback;

Rollback complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:20:25 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

no rows selected

结论得证,操作被回滚(rollback)了。

4)默认情况下小结
  以上三种情况中,重点关注第一种行为,就是在没有显式提交和回滚的情况下使用exit命令退出SQL*Plus后,之前的操作将默认的生效(commit)。

5.在讨论完默认情况之后,我们再讨论一下当AUTOCOMMIT保持默认“OFF”不变,将EXITCOMMIT修改为“OFF”的三种情况
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
OFF         OFF         -         ROLLBACK
OFF         OFF         COMMIT    COMMIT
OFF         OFF         ROLLBACK  ROLLBACK

1)第一种行为实验:AUTOCOMMIT为OFF,EXITCOMMIT为OFF,直接退出SQL*Plus
sec@ora11g> show AUTOCOMMIT
autocommit OFF
sec@ora11g> set EXITCOMMIT off
sec@ora11g> show EXITCOMMIT
exitcommit OFF
sec@ora11g> insert into t values (1);

1 row created.

sec@ora11g> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:28:20 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

no rows selected

  结论得证,操作退出之前的操作被回滚了。

2)第二种行为实验:AUTOCOMMIT为OFF,EXITCOMMIT为OFF,显式commit后,再退出SQL*Plus
sec@ora11g> show AUTOCOMMIT
autocommit OFF
sec@ora11g> set EXITCOMMIT off
sec@ora11g> show EXITCOMMIT
exitcommit OFF
sec@ora11g> insert into t values (2);

1 row created.

sec@ora11g> commit;

Commit complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:30:25 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         2

  结论得证,操作被提交,符合commit的本质要求。

3)第三种行为实验:AUTOCOMMIT为OFF,EXITCOMMIT为OFF,显式rollback后,再退出SQL*Plus
sec@ora11g> show AUTOCOMMIT
autocommit OFF
sec@ora11g> set EXITCOMMIT off
sec@ora11g> show EXITCOMMIT
exitcommit OFF
sec@ora11g> insert into t values (3);

1 row created.

sec@ora11g> rollback;

Rollback complete.

sec@ora11g> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:35:44 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

no rows selected

结论得证,显式rollback之后,数据被回滚了。

4)当AUTOCOMMIT保持默认“OFF”不变,将EXITCOMMIT修改为“OFF”小结
  重点关注的还是第一种情况,在不显式的提交commit和rollback命令直接退出SQL*Plus,操作是会被回滚的。

6.剩下的AUTOCOMMIT为“ON”的情形我们统一在这里做一下实验,虽然结论显然不过为了实验的完整性,将真实的演示过程罗列在此
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
ON          ON          -         COMMIT
ON          OFF         -         COMMIT
ON          ON          COMMIT    COMMIT
ON          ON          ROLLBACK  COMMIT
ON          OFF         COMMIT    COMMIT
ON          OFF         ROLLBACK  COMMIT

1)第一种行为实验:AUTOCOMMIT为ON,EXITCOMMIT为ON,直接再退出SQL*Plus
sec@ora11g> set AUTOCOMMIT on
sec@ora11g> set EXITCOMMIT on
sec@ora11g> insert into t values (1);

1 row created.

Commit complete.
sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:43:19 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         1

  数据确实被提交了,结论得证。

2)第二种行为实验:AUTOCOMMIT为ON,EXITCOMMIT为OFF,直接再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> set AUTOCOMMIT on
sec@ora11g> set EXITCOMMIT off
sec@ora11g> insert into t values (2);

1 row created.

Commit complete.
sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:48:28 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         2

  细心的您一定发现了有一行提示信息写着“Commit complete.”,的确是自动提交了数据,实验结论得证。

3)第三种行为实验:AUTOCOMMIT为ON,EXITCOMMIT为ON ,commit后再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> set AUTOCOMMIT on
sec@ora11g> set EXITCOMMIT on
sec@ora11g> insert into t values (3);

1 row created.

Commit complete.
sec@ora11g> commit;

Commit complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:50:01 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         3

  实验结论得证,这里再次输入commit貌似有点多余,不过为了实验的完整性,还是操作了一次。实验结论得证。

4)第四种行为实验:AUTOCOMMIT为ON,EXITCOMMIT为ON ,rollback后再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> set AUTOCOMMIT on
sec@ora11g> set EXITCOMMIT on
sec@ora11g> insert into t values (4);

1 row created.

Commit complete.
sec@ora11g> rollback;

Rollback complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:52:14 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         4

  实验结论显然,因为已经隐式的提交了,再次的rollback已经于事无补。结论得证。

5)第五种行为实验:AUTOCOMMIT为ON,EXITCOMMIT为OFF,commit后再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> set AUTOCOMMIT on
sec@ora11g> set EXITCOMMIT off
sec@ora11g> insert into t values (5);

1 row created.

Commit complete.
sec@ora11g> commit;

Commit complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:55:10 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         5

  此处是多此一举的commit,因为之前已经隐式的提交了。结论得证。

6)第六种行为实验:AUTOCOMMIT为ON,EXITCOMMIT为OFF,rollback后再退出SQL*Plus
sec@ora11g> truncate table t;

Table truncated.

sec@ora11g> set AUTOCOMMIT on
sec@ora11g> set EXITCOMMIT off
sec@ora11g> insert into t values (6);

1 row created.

Commit complete.
sec@ora11g> rollback;

Rollback complete.

sec@ora11g> exit

[oracle@secDB ~]$ sqlplus sec/sec

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 14 22:56:27 2012

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sec@ora11g> select * from t;

         X
----------
         6

  结论显然得证。

7)AUTOCOMMIT为“ON”的情形小结
  因为AUTOCOMMIT参数表示“隐式提交”,所以在每次操作后都会紧跟着commit,根据commit的原理,结论显然,上述六种情形的结论相同:操作被真正的提交。

7.小结
  上面通过三类讨论并验证了结论。为加深印象并使条理更加清晰,把三类结论在此汇总在此,便于参考
1)SQL*Plus默认情况下:重点关注第一条结论,其他两条是常识性结论
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
OFF         ON          -         COMMIT
OFF         ON          COMMIT    COMMIT
OFF         ON          ROLLBACK  ROLLBACK

2)AUTOCOMMIT保持默认“OFF”不变,将EXITCOMMIT修改为“OFF”的三种情况:重点放在第一条上
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
OFF         OFF         -         ROLLBACK
OFF         OFF         COMMIT    COMMIT
OFF         OFF         ROLLBACK  ROLLBACK

3)剩下的AUTOCOMMIT为“ON”的情形,结论统统是“提交”
AUTOCOMMIT  EXITCOMMIT  EXIT      Exit Behavior
ON          ON          -         COMMIT
ON          OFF         -         COMMIT
ON          ON          COMMIT    COMMIT
ON          ON          ROLLBACK  COMMIT
ON          OFF         COMMIT    COMMIT
ON          OFF         ROLLBACK  COMMIT

  通过对Oracle 11gR2引入的这个EXITCOMMIT参数的全面实验与理解,我们可以得到一个结论:实质上引入这个参数的目的就是对非显式“commit”和“rollback”下直接退出SQL*Plus的行为进行了规范,即上面第一类的第一条结论和第二类的第一条结论。

  如果您可以静下心来演练一下文中所做的实验,您也一定会有所获(不仅仅是实验结论上的收获)。

Good luck.

secooler
12.03.14

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-617655/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,OCM联盟(ocmu.org)创始人,恩墨学院(enmoedu.com)创始人,ITPUB Oracle专题深入讨论版版主,资深Oracle数据库专家,北京大学理学硕士,获Oracle OCM 10g 11g认证,ACOUG核心成员,DATAGURU专家团成员,Blogger。Good luck.

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7885418