首先来看正常exit sqlplus的情况
SQL> create tablespace remen datafile 'e:\oracle\oradata\ora9\remen01.dbf' size 10m;
表空间已创建。
SQL> create user remen identified by remen default tablespace remen ;
用户已创建
SQL> grant connect,resource to remen;
授权成功。
SQL> connect remen/remen
已连接。
SQL> create table test (id number);
表已创建。
SQL> insert into test values (1);
已创建1行。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开
C:\Documents and Settings\user>sqlplus remen/remen
SQL*Plus: Release 9.2.0.1.0 - Production on星期五12月1 13:40:04 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from test;
ID
----------
1
SQL>
(结论:只要正常退出,如果你没有执行commit,sqlplus会帮你执行commit)
………………………………………………………….
下面我们来模拟另外一种情况,就是执行insert后,session异常断开(比如被kill)
SQL> insert into test values (2);
已创建1行。
SQL>
新开一个窗口,KILL这个进程
C:\Documents and Settings\user>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on星期五12月1 13:57:06 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select SID,SERIAL#,USERNAME from v$session where username='REMEN';
SID SERIAL# USERNAME
---------- ---------- ------------------------------
11 10 REMEN
SQL> alter system kill session '11,10';
系统已更改。
SQL>
返回刚才那个insert的窗口,发现session被删去
SQL> EXIT
ERROR:
ORA-00028:您的会话己被删去
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production(情况复杂)中断开
然后重新登陆,察看是否完成insert
C:\Documents and Settings\user>sqlplus remen/remen
SQL*Plus: Release 9.2.0.1.0 - Production on星期五12月1 13:54:27 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from test;
ID
----------
1
SQL>
(结论:如果进程异常退出,系统会自动rollback,所以insert无效)
…………………………………………………………………….
另外oracle还有一个autocommit的说法,和我们讨论的这个是否有联系呢?试验如下:
SQL> select * from test;
ID
----------
1
SQL> set autocommit on;
SQL> insert into test values(2);
已创建1行。
提交完成。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开
C:\Documents and Settings\user>sqlplus remen/remen
SQL*Plus: Release 9.2.0.1.0 - Production on星期五12月1 14:14:11 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from test;
ID
----------
1
2
SQL>
(这个是所谓的自动提交,只要你发出insert,在插入完成后,紧接着系统就会发出一条comit动作,区别于前面所讲到的exit后才由系统执行commit)
注意:正常退出后系统系统提交是sqlplus本身的特点,无法修改
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16566331/viewspace-629299/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16566331/viewspace-629299/