oralce数据库的回滚操作原理

  1 打开分析参数
SQL> set autotrace traceonly statistics


2 创建测试用的表格
SQL> create table t as select * from all_objects where 1=0;

表已创建。

SQL> desc t;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)


3 看看表格里的数据，首次读取
SQL> select * from t;

未选定行


统计信息
----------------------------------------------------------
        264  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

4 再次查看，发现不再进行磁盘读取了
SQL> select * from t;

未选定行


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


5 插入大量的数据，伴随着大量的磁盘操作
SQL> insert into t select * from all_objects;

已创建49883行。


统计信息
----------------------------------------------------------
      57484  recursive calls
       4139  db block gets
      67164  consistent gets
        290  physical reads
    5623980  redo size
        670  bytes sent via SQL*Net to client
        575  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
        348  sorts (memory)
          0  sorts (disk)
      49883  rows processed

6 马上回滚
SQL> rollback;

回退已完成。

7 再次查看磁盘操作情况，发现有较大的数据读取
SQL> select * from t;

未选定行


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        689  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

8 再次查看，同样的结果
SQL> select * from t;

未选定行


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        689  consistent gets
          0  physical reads
          0  redo size
        992  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL>

9 结论：
回滚并不是让磁盘恢复到以前的样子。前面的INSERT 操作将一些块增加到表的高水位线（high-water mark，HWM）之下，这些块没有因为回滚而消失，它们还在那里，而且已经格式化，只不过现在为空。全表扫描必须读取这些块，看看其中是否包含行。这说明，回滚只是一个“将数据库还原“的逻辑操作。数据库并非真的还原成原来的样子，只是逻辑上相同而已。因此杜思波得出一个结论就是，oracle的rollback只是逻辑上保持了数据库回复到了上一步。