Flashback query(undo)

select * from table_name as of timestamp

to_timestamp('2008-03-03 12:00:00','yyyy-mm-dd hh24:mi:ss');

select * from table_name as of scn 123456;

Flashback table(undo,恢复表到某时间点，阻塞写操作)

--必须开启行移动

alter table table_name enable row movement;

flashback table table_name to timestamp(JUL-07-2008,02:33:00);

flashback table table_name to scn 123456;

flashback table table_name to timestamp '2008-03-03 12:05:00' enable triggers;

--只恢复trigger或index状态，不恢复更改

Flashback drop(recyclebin)

--管理recyclebin

show recyclebin;

select * from user_recyclebin;

select * from dba_recyclebin;

purge recyclebin;

--闪回drop表

flashback table table_name to before drop [rename to new_name];

--recyclebin中有同名，闪回最近删除的；闪回后有同名表，需rename

Flashback database(flashback log,redo log,archive log)

--开启flashback database

shutdown immediate

startup mount

alter database flashback on --必须先开启归档

--闪回数据库(mount状态,read only方式检查,resetlogs方式打开)

flashback database to time to_date(xxx);

flashback database to time to_timestamp(xxx);

flashback database to scn xxx;

flashback database to sequence xxx thread 1;

flashback database to timestamp(sysdate-1/24);

--管理闪回区

db_recovery_file_dest --闪回区路径

db_recovery_file_dest_size --闪回区大小

db_flashback_retention_target --闪回数据保存时间

select * from v$flash_recovery_area_usage; --闪回区使用率

select * from v$flashback_database_log; --闪回区情况，如能恢复到的最早的时间点

Flashback version query(undo)

--查询表的多个版本，记录行的改变

select versions_starttime,versions_endtime,versions_startscn,versions_endscn,

versions_xid,versions_operation,a,b

from test versions between timestamp minvalue and maxvalue

order by versions_starttime;

Flashback transaction query(undo)

--查看当前事务号

select xid from v$transaction;

--查看undo_sql

select undo_sql from flashback_transaction_query

where xid =hextoraw('xxxxxxx'); --不使用hextoraw很难利用系统的索引