Oracle SQL*Plus 退出Commit or Rollback设置

背景

  《Oracle-Java JDBC 连接超时之后的认知纠正》这篇文章之后，结合官方文档，本文关于SQL*Plus 退出Commit or Rollback设置的参数进行测试。弥补一下自己的知识盲区。

SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}

官方文档介绍

译

• 控制Oracle数据库在执行SQL或PL/SQL命令后何时向数据库提交挂起的更改。
• ON：在Oracle数据库执行每个成功的INSERT、UPDATE、DELETE或PL/SQL块后，向数据库提交处理的更改。
• OFF：禁止自动提交，因此您必须手动提交更改（例如，使用SQL命令commit）。
• IMM[EDIATE] | n：在Oracle数据库执行n个成功的SQL INSERT、UPDATE或DELETE命令或PL/SQL块后，IMMEDIATE函数以与ON.n相同的方式向数据库提交挂起的更改。n不能小于零或大于200000000。在成功完成n个INSERT、UPDATE或DELETE命令或PL/SQL块、提交、回滚或SET AUTOCOMMIT命令后，语句计数器重置为零。
• 当SQL*Plus退出时，SET AUTOCOMMIT不会改变提交行为。默认情况下，任何未提交的数据都会被提交。

测试-1：默认情况下(SET AUTOCOMMIT OFF)，任何未提交的数据都会被提交

TWO@phytest1:74> show auto
autocommit OFF
TWO@phytest1:74> select * From t1;
no rows selected
TWO@phytest1:74> insert into t1 values(1,userenv('sid'),'xx');
1 row created.
TWO@phytest1:74> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db ~]$ sqlplus two/two
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 17:09:56 2024
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TWO@phytest1:74> select * from t1;
        ID SID                  NAME
---------- -------------------- --------------------------------------------------
         1 74                   xx

测试-2：SET AUTOCOMMIT ON/IMM (自动提交开)

TWO@phytest1:74> SET AUTOCOMMIT ON
TWO@phytest1:74> select * from t1;
no rows selected
TWO@phytest1:74> insert into t1 values(1,userenv('sid'),'xx');
1 row created.
Commit complete.
TWO@phytest1:74> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db ~]$ sqlplus two/two
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 17:11:25 2024
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TWO@phytest1:74> select * From t1;

        ID SID                  NAME
---------- -------------------- --------------------------------------------------
         1 74                   xx

测试-3：SET AUTOCOMMIT N

• N : DML语句的个数
  

SET EXITC[OMMIT] {ON | OFF}

官方文档介绍

译

• 指定默认EXIT行为是COMMIT还是ROLLBACK。
• 默认设置为ON，这意味着在退出时提交工作，无论您是否期望提交。将EXITCOMMIT设置为OFF，以便在退出时回滚工作。

显示了与SET命令（AUTOCOMMIT和EXITCOMMIT）和exit命令组合相关的退出操作

测试：

• AUTOCOMMIT= OFF (默认)
• EXITCOMMIT= ON (默认)
• EXIT = ROLLBACK

TWO@phytest1:74> show auto
autocommit OFF
TWO@phytest1:74> show exitc
exitcommit ON
TWO@phytest1:74> select * from t1;
no rows selected
TWO@phytest1:74> insert into t1 values(1,userenv('sid'),'xx');
1 row created.
TWO@phytest1:74> rollback;
Rollback complete.
TWO@phytest1:74> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db ~]$ sqlplus two/two
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 17:50:19 2024
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TWO@phytest1:74> select * from t1;
no rows selected

小结

  在数据库运维中，Session的有效退出是保障系统稳定与安全的核心环节。无论是用户主动断开、超时释放还是异常中断，Session退出过程直接影响资源回收效率与潜在风险防控。若管理不当，残留会话可能导致连接池耗尽、资源泄漏甚至安全漏洞。