1.Flashback借助undo记录数来确定恢复多少数据,同时,在数据DDL更改表后,不能Flashback到之前的状态
2.Flashbackup不能回滚,不过可以指定过去的时间来恢复,建议在Flashback之前记录当前的SCN号.
3.Row movement 必须启启用(alter table t1 enable row movement)才能使用To before drop语句(flashback table t1 to before drop)
该步骤使用Recyclebin而不是undo来恢复drop的表.
4.Flashback表到指定的时间点,必须拥有Select any dictionary、Flashback any table系统权限或者Select_catalog_role角色
5.Flashback table t1 to before drop不能恢复相关的级联约束.
=========================================示例=======================
1.闪回表到Drop之前状态.(to before drop,不需要表行迁移)
FLASHBACK TABLE print_media TO BEFORE DROP RENAME TO print_media_old;
SQL> create table t1 as select * from dba_objects where rownum<=10;
SQL> drop table t1;
Table dropped.
SQL> select * from t1;
select * from t1 *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select object_name from user_recyclebin;
OBJECT_NAME
------------------------------------------------------------
BIN$21HBuSt7vk/gQAAAAABCmQ==$0
SQL> flashback table t1 to before drop;
Flashback complete.
SQL> select count(*) from t1;
COUNT(*)
----------
10
2.闪回到Timestamp(或者scn,Timestamp_to_scn函数)
1)
create table t(id number,tim timestamp);
insert into t values(1,SYSTIMESTAMP );
insert into t values(1,SYSTIMESTAMP );
commit;
ID TIM
---------- ---------------------------------------------------------------------------
1 27-4月 -13 01.29.34.281496 下午
1 27-4月 -13 01.29.52.135195 下午
1 27-4月 -13 01.31.29.919566 下午
2)模拟误删除数据(需要将表启动行迁移)
delete from t;
commit;
3)当前时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2013-04-27 13:33:14
4)闪回到2013-04-27 13:32:00的时间
flashback table t to timestamp to_timestamp('2013-04-27 13:32:00.000000','YYYY-MM-DD HH24:MI:SS.FF');
SQL> flashback table t to timestamp to_timestamp('2013-04-27 13:32:00.000000','YYYY-MM-DD HH24:MI:SS.FF');
flashback table t to timestamp to_timestamp('2013-04-27 13:32:00.000000','YYYY-MM-DD HH24:MI:SS.FF')
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table t enable row movement;
Table altered.
SQL> flashback table t to timestamp to_timestamp('2013-04-27 13:32:00.000000','YYYY-MM-DD HH24:MI:SS.FF');
Flashback complete.
恢复完成
SQL> select * from t;
ID TIM
---------- ---------------------------------------------------------------------------
1 27-4月 -13 01.29.34.281496 下午
1 27-4月 -13 01.29.52.135195 下午
1 27-4月 -13 01.31.29.919566 下午
