閃回和drop原 undo tbs的一點分析

Flashback閃回，表級閃回是由undo回滾段作支撐的（表的結構不能改變，不能alter，truncate等ddl操作），而10g的閃回drop的表是由於表空間的虛擬回收站來閃回的，被刪除的表只是被重新命名而已，在空間足夠情況下並沒有徹底清除。而10g的資料庫級的閃回則是又閃回日誌來支撐的！

表級閃回跟undo表空間

SQL> select * from xiaoyu.test03;

ID

----------

44

11

22

33

44

11

22

33

8 rows selected

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

2133219

SQL> insert into xiaoyu.test03 select * from xiaoyu.test03;

8 rows inserted

SQL> commit;

Commit complete

SQL> create undo tablespace undotbspace01 datafile 'd:oracleproduct10.2.0oradataxiaoyuundotbspace01.dbf' size 50M;

Tablespace created

SQL> alter system set undo_tablespace=undotbspace01;

System altered

SQL> drop tablespace undotb including contents and datafiles;

Tablespace dropped

SQL> select * from xiaoyu.test03 as of scn 2133219;

select * from xiaoyu.test03 as of scn 2133219

ORA-01555: 快照過舊: 回退段號 (名稱為 "") 過小

可以看出drop原xiaoyu.test03的在insert時期的回滾段所在的回滾表空間，閃回查詢時已經無法查詢到以前的回滾段出現ora-01555

再看下面的這個例子：

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

2134728

SQL> create undo tablespace undotbs02 datafile 'd:oracleproduct10.2.0oradataxiaoyuundotbs02.dbf' size 50M;

Tablespace created

SQL> insert into xiaoyu.test03 select * from xiaoyu.test03;

32 rows inserted

SQL> commit;

Commit complete

SQL> alter system set undo_tablespace=undotbs02;

System altered

SQL> select count(*) from xiaoyu.test03;

COUNT(*)

----------

64

SQL> select count(*) from xiaoyu.test03 as of scn 2134728;

COUNT(*)

----------

32

SQL> drop tablespace undotbs01 including contents and datafiles;

Tablespace dropped

SQL> select count(*) from xiaoyu.test03 as of scn 2134728;

COUNT(*)

----------

32

看出第二個例子即使刪除了原undo的回滾段資訊，還是可以進行基於原undo表空間所記錄的回滾段的閃回查詢，第二個例子在刪除原undo表空間前進行了一次閃回查詢，個人推斷刪除表空間前的閃回查詢獲取了原undo表空間的undo段儲存在cache buffer中並結合block構造cr塊，所以drop原undo tablespace後閃回查詢可以從cache buffer中獲取undo block進行查詢。

[@more@]