undo用於將資料庫邏輯的恢復到原來的樣子

jelephant發表於2013-12-07
1、建立一個表
JEL@JEL >create table t as select * from all_objects where 0=1;

Table created.

JEL@JEL >select * from t;

no rows selected

2、查詢表時IO情況
JEL@JEL >SET AUTOTRACE TRACEONLY STATISTICS;
JEL@JEL >select * from t;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        369  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
JEL@JEL >set autotrace off;

3、插入資料並回滾
JEL@JEL >insert into t select * from all_objects;

9258 rows created.

JEL@JEL >select count(*) from t;

  COUNT(*)
----------
      9258

JEL@JEL >rollback;

Rollback complete.

JEL@JEL >select count(*) from t;

  COUNT(*)
----------
         0

4、查詢表時的IO情況
JEL@JEL >set autotrace traceonly statistics;
JEL@JEL >select * from t;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        120  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        369  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

JEL@JEL >set autotrace off;

insert導致將一些塊增加到表的HWM之下,這些塊並沒有因為回滾而消失,而是已經格式化,只不過現在為空。全表掃描必須讀取這些塊,看看其中是否包含行。這說明,回滾只是一個將資料庫還原的邏輯操作。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29337971/viewspace-1062661/,如需轉載,請註明出處,否則將追究法律責任。

相關文章