oracle11g sqlplus参数autocommit

最新推荐文章于 2024-09-15 20:53:06 发布

clg10051

最新推荐文章于 2024-09-15 20:53:06 发布

阅读量200

点赞数 1

文章标签：数据库后端

结论

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>