【SQL*Plus】使用Oracle 11gR2的EXITCOMMIT引數控制在SQL*Plus中exit時commit的行為

secooler發表於2012-03-14
  偶然間邂逅了這個在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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章