【SQL*Plus】使用Oracle 11gR2的EXITCOMMIT引數控制在SQL*Plus中exit時commit的行為
偶然間邂逅了這個在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 --
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sqlplus 中exit 命令的隱式commitSQLMIT
- SQL*Plus Set引數詳解SQL
- 使用OEM,SQL*Plus,iSQL*Plus 管理Oracle例項SQLOracle
- 使用SQL*PlusSQL
- 【SQL*Plus】在SQL*Plus中謹慎使用Ctrl+S快捷鍵SQL
- SQL/PLUS命令的使用大全SQL
- SQL*PLUS命令的使用大全SQL
- 【SQL*Plus】使用SQL*Plus的Preliminary方式連線資料庫SQL資料庫
- 【SQL*Plus】使用SQL*Plus的-S選項精簡輸出資訊SQL
- oracle中SQLPLUS的Login.sqlOracleSQL
- ORACLE SQL and SQL*PLUS (strong recommend)OracleSQL
- 【sqlplus】SQL*Plus命令使用大全SQL
- Oracle使用SQL*Plus生成html檔案OracleSQLHTML
- 使用sql*plus編輯sql文字SQL
- 在shell中執行SQL*Plus命令SQL
- 【SQL*Plus】11g版本對 SQL*Plus錯誤日誌的記錄功能——errorlogging引數SQLError
- 使用sql*plus時的一個安全小問題SQL
- 預定義的SQL*Plus變數SQL變數
- 【SQL*Plus】直接X掉視窗對SQL*Plus中的操作結果的影響SQL
- SQL*Plus的行編輯命令(轉)SQL
- SQL*PLUS 環境變數SQL變數
- 常用的sql*plus命令：SQL
- SQL*PLUS命令的使用大全（一）薦SQL
- SQL*PLUS命令的使用大全(zt)(轉)SQL
- 【SQL*Plus】使用BREAK和COMPUTE在SQL*Plus中得到分組統計結果SQL
- SQL*Plus中使用DATE型別的繫結變數SQL型別變數
- sql plus命令使用總結SQL
- Oracle 11g系列：SQL Plus與PL/SQLOracleSQL
- Oracle通過login.sql實現永久修改sqlplus引數OracleSQL
- 【優化】ORACLE set autotrace in SQL*Plus優化OracleSQL
- sql*plus會話環境相關的引數設定檔案SQL會話
- plustrce.sql的作用SQL
- 設定sql plus 的autotraceSQL
- 【SQL*Plus】處理 SQL*Plus的標頭無法顯示問題SQL
- 定義SQL*PLUS型別的可執行SQL型別
- 【SQL*Plus】11g中使用SQL*Plus的-S選項依然可以看到“Enter password:”問題SQL
- 【SQL/PLUS】Oracle PL/SQL程式設計用set serveroutput onSQLOracle程式設計Server
- sqlplus中glogin.sql的例子SQL