shutdown abort导致事务提交?

前不久,同事问了我一个这样的问题,并且附上了实验记录,在oracle中,为何shudown abort后导致了事务的提交,而并非回滚?现将他的实验记录贴出来,细心的朋友可能会马上发现问题。

SQL> conn scott/scott
Connected.
SQL> set line 100 pagesize 200
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7499 ALLEN      SALESMAN	      7698 19810220 00:00:00	   1600        300	   30
      7521 WARD       SALESMAN	      7698 19810222 00:00:00	   1250        500	   30
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7654 MARTIN     SALESMAN	      7698 19810928 00:00:00	   1250       1400	   30
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7844 TURNER     SALESMAN	      7698 19810908 00:00:00	   1500 	 0	   30
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

14 rows selected.

SQL> delete from emp where comm is not null;

4 rows deleted.

SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

10 rows selected.

SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size		    2270072 bytes
Variable Size		 3.6776E+10 bytes
Database Buffers	 9.7979E+10 bytes
Redo Buffers		  197844992 bytes
Database mounted.
Database opened.
SQL> conn scott/scott
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE		    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 19801217 00:00:00	    800 		   20
      7566 JONES      MANAGER	      7839 19810402 00:00:00	   2975 		   20
      7698 BLAKE      MANAGER	      7839 19810501 00:00:00	   2850 		   30
      7782 CLARK      MANAGER	      7839 19810609 00:00:00	   2450 		   10
      7788 SCOTT      ANALYST	      7566 19870419 00:00:00	   3000 		   20
      7839 KING       PRESIDENT 	   19811117 00:00:00	   5000 		   10
      7876 ADAMS      CLERK	      7788 19870523 00:00:00	   1100 		   20
      7900 JAMES      CLERK	      7698 19811203 00:00:00	    950 		   30
      7902 FORD       ANALYST	      7566 19811203 00:00:00	   3000 		   20
      7934 MILLER     CLERK	      7782 19820123 00:00:00	   1300 		   10

10 rows selected.

同事前来问我,为何重启后,事务提交了?不是应该回滚的么?

细心的同学可能已经发现了问题,执行完事务后,会话进行shutdown abort失败(权限不足),使用了conn 命令进行了用户的切换,引用Oracle官方文档中的描述”:

Usage

CONNECT commits the current transaction to the database, disconnects the current username from Oracle Database, and reconnects with the specified username.

这个问题虽然看起来过于细节也很基础,但是稍不注意便会造成数据的隐式COMMIT,特别是在SQLPLUS中操作数据的过程中,需要特别小心。