oracle闪回与undo,闪回和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@]