简单使用基于SCN的flashback，10.2.0.1 for windows

实验如下:

SQL> select * from tt;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1168329

SQL> delete from tt where rownum<2;

已删除 1 行。

SQL> commit;

提交完成。

SQL> select * from tt;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> select * from tt as of scn 1168329;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> create table tt_reco as select * from tt where 1=0;

表已创建。

SQL> insert into tt_reco select * from tt as of scn 1168329;

已创建4行。

SQL> commit;

提交完成。

SQL> select * from tt_reco;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> select count(*) from tt;

COUNT(*)
----------
3

SQL> truncate table tt;

表被截断。

SQL> insert into tt select * from tt_reco ;

已创建4行。

SQL> commit;

提交完成。

SQL> select * from tt;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

[@more@]

基于时间的flashback实验

SQL> select * from tt;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
28-7月 -08 02.54.56.921000 下午 +08:00

SQL> delete from tt where rownum<2;

已删除 1 行。

SQL> commit;

提交完成。
SQL> select * from tt;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> select * from tt as of timestamp(systimestamp -interval'480' second);

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> create table tt_reco as select * from tt where 1=0;

表已创建。

SQL> select * from tt as of timestamp(systimestamp -interval'480' second);
select * from tt as of timestamp(systimestamp -interval'680' second)
*
第 1 行出现错误:
ORA-01466: 无法读取数据 - 表定义已更改

SQL> select * from tt as of timestamp(systimestamp -interval'780' second);

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> insert into tt_reco select * from tt as of timestamp(systimestamp -interval'780' second);

已创建4行。

SQL> commit;

提交完成。

SQL> select * from tt_reco;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE

SQL> drop table tt;

表已删除。

SQL> create table tt as select * from tt_reco where 1=0;

表已创建。

SQL> insert into tt select * from tt_reco;

已创建4行。

SQL> commit;

提交完成。

SQL> select * from tt;

NAME STATUS
-------------------------------------------------- -------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF SYSTEM
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DB ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF ONLINE
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF ONLINE