flashback學習筆記

flashback
------------------------------


1.flashback query閃回查詢 (undo )
_________________________________
select current_scn from v$database;


select * from t as of scn 2387032;






select systimestamp from dual;
select scn_to_timestamp(2387032) from dual;
select timestamp_to_scn('03-NOV-14 06.47.55.000000000 PM') from dual;


select * from t as of timestamp sysdate-5/1440;

SYSDATE-5/1440是啥意思，1440又是怎麼來的？
首先60（分）×24=1440，這樣就計算出一天擁有多少分鐘，SYSDATE是系統函式，用來取得當前的系統時間（以天為單位），SYSDATE-5/1440，得出的就是距當前時間5分鐘前的記錄了。後面示例中需要計算之前的某個時段時，均是使用這一方法。


2.flashback table (undo)
___________________
flashback table t to scn 2387032;


flashback table  t to timestamp to_timestamp('2009-1-4 22:21:11', 'yyyy-mm-
dd hh24:mi:ss');


ora_08189： cannot flashback the table because movemont is not enable;
解決：
select row_movement from dba_tables where table_name='T' and owner='HR';
alter table t enable row movement;


alter table t disable row movement;


3.flashback versions query (undo)
______________________________


--查詢偽列
select versions_startscn,versions_endscn,versions_xid,versions_operation,versions_starttime,versions_endtime,id,name from t versions between scn minvalue and maxvalue oreder by versions_startscn;


4.flashback transaction query(undo)
_____________________________


select * from flashback_transaction_query where where xid='...'




5.flashback drop (table)


show recyclebin;
select * from dba_recyclebin;
select * from dba_objects where object_name='BIN$BRCtqVHonmLgUAB/AQAXIQ==$0';


flashback table "BIN$BRCtqVHonmLgUAB/AQAXIQ==$0" to before drop;


閃回drop 前的表是，索引及觸發器都能閃回，但名字是回收站的名字，需手工重新命名；


alter index  "BIN$BRCtqVHonmLgUAB/AQAXIQ==$0" rename to idx_t1;


6.flashback database
_____________________


select flashback_on from v$database;


前提：
1.archivelog 模式
2.flash recovery area(flashback log);


alter database archivelog;
alter database flashback on;


select * from v$bgprocess where paddr<>'00';




----------------------------------------
select * from v$flashback_database_stat;
select * from v$flashback_database_log;
select * from v$flashback_database_logfile;


mount 狀態下


flashback database to scn 2387032;