1、查看当前SCN并且转换为时间
select scn_to_timestamp(dbms_flashback.get_system_change_number) from dual;
select timestamp_to_scn(to_date('2013-08-2 10:01:00','yyyy-mm-dd hh24:mi:ss')) from dual;
2、开启和使用flashback database
--必须开启归档
--必须设置参数
db_flashback_retention_target
db_recovery_file_dest
db_recovery_file_dest_size --如果查过快速恢复区大小，FLASHBACK LOG会自动删除
--mount节点设置开启FLASHBACK DATABASE ON
alter database flashback on;
可以从v$FLASHBACK_DATABASE_LOG视图中获得OLDEST_FLASHBACK_SCN或者OLDEST_FLASHBACK_TIME，以确定FLASHBACK DATABASE能够恢复到最早的时刻。
然后mount，RMAN下执行
flashback database to scn=689316;
flashback database to time="04-9月-2008 11:05:00";
最后open resetlogs
如果视图恢复可恢复SCN以前的就会报错
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 08/02/2013 09:03:28
ORA-38729: Not enough flashback database log data to do FLASHBACK.
3、闪回表（flashback table）
FLASHBACK TABLE
ALTER TABLE TEST enable row movement;
flashback table TEST to timestamp SYSDATE-1;
flashback table pp123 to timestamp (to_date('2013-08-2 10:01:00','yyyy-mm-dd hh24:mi:ss'));
flashback table pp123 to scn **
4、闪回drop的表（flashback drop）
--参数recyclebin 设置为on（默认）
--依赖于recyclebin
flashback table test_re to before drop; --索引会随着一起DROP到recyclebin
alter index "BIN$4u6QyIlxGzzgQAB/AQAl8Q==$0" rename to test; --重命令你的索引
flashback table "BIN$4u6QyIl7GzzgQAB/AQAl8Q==$0" to before drop;--如果有同名需要这样恢复
select * from "BIN$4u6QyImDGzzgQAB/AQAl8Q==$0";--你可以查看回收站
drop table test_re purge;--你可以手动避开回收站
purge user_recyclebin;--清理本OWNER下回收站
purge dba_recyclebin;--清理所有OWNER的回收站需要sysdba.
5、闪回查询
SELECT * FROM test
AS OF TIMESTAMP TO_TIMESTAMP
('2013-08-04 10:22:00', 'yyyy-mm-dd hh24:mi:ss') ;
6、FLASHBACK VERISON(注意UPDATE,DELETE会生成2行如下）
SELECT versions_startscn,
versions_starttime,
versions_endscn,
versions_endtime,
versions_xid,
versions_operation,
username
FROM testll VERSIONS BETWEEN TIMESTAMP (to_date('2013-08-2 23:34:07','yyyy-mm-dd hh24:mi:ss')) AND sysdate
where versions_startscn is not null or versions_endscn is not null;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME
----------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ---------------- ------------------ ------------------------------
33860567 02-AUG-13 11.41.51 PM 0D001300311D0000 U ooo
33860342 02-AUG-13 11.36.17 PM 0E0027002F1D0000 D SYSTEM
33860309 02-AUG-13 11.35.07 PM 0D001200311D0000 D SYS
33860309 02-AUG-13 11.35.07 PM SYS
33860342 02-AUG-13 11.36.17 PM SYSTEM
33860567 02-AUG-13 11.41.51 PM PPZHU
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME
----------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ---------------- ------------------ ------------------------------
33861435 03-AUG-13 12.02.00 AM 0F000000321D0000 I SYS
7、闪回事务（可以找到回退语句，但是查询相当慢，使用不当会影响生产系统性能））
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D');
SELECT xid, logon_user FROM flashback_transaction_query
WHERE xid IN (SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));