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很难利用系统的索引
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10176825/viewspace-1023415/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10176825/viewspace-1023415/