Oracle Flashback命令手册

2019独角兽企业重金招聘Python工程师标准>>>

Flashback 

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很难利用系统的索引

转载于:https://my.oschina.net/lovedreamland/blog/307448