Oracle Flashback命令手冊

標籤：des   使用   io   for   ar   資料   art   cti   log   

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很難利用系統的索引

Oracle Flashback命令手冊