闪回查询、闪回表、闪回数据库(delete,update,drop及一切混合动作)

--===================情景1(inset、delete表)=================================

select * from location t;delete from LOCATION where LOCATION_id in (23,123);commit;select * from LOCATION order by 1;select * from LOCATION as of timestamp sysdate-5/24/60 order by 1;select * from LOCATION as of timestamp sysdate-5/24/60minusselect * from LOCATION;insert into LOCATION select * from LOCATION as of timestamp sysdate-5/24/60minusselect * from LOCATION;

--========================情景2(update表)：=================================

update LOCATION set LOCATION_no='99' where LOCATION_id in (23,123);commit;select * from LOCATION where LOCATION_id in (23,123);select * from LOCATION as of timestamp sysdate-2/24/60 where LOCATION_id in (23,123);select * from LOCATION as of timestamp sysdate-2/24/60minusselect * from LOCATION;select versions_starttime,versions_endtime,versions_operation,versions_xid,a.* from LOCATION versions  between timestamp minvalue and maxvalue a  where LOCATION_id in (23,123) order by 1,2;select * from flashback_transaction_query where xid=hextoraw('10000C00C0020000')order by undo_change# DESC;update "HELIOS"."LOCATION" set "LOCATION_NO" = '3' where ROWID = 'AAAfaIAAHAAAAUFAAB';update "HELIOS"."LOCATION" set "LOCATION_NO" = '1' where ROWID = 'AAAfaIAAHAAAAUFAAA';commit;

Oracle 11g Flashback_transaction_query的undo_sql为空？

      近日测试的时候发现 flashback_transaction_query中 undo_sql 为空，经查证这个问题是 Oracle 11g 默认把 supplemental logging 禁用了导致的。使用如下语句，把 supplemental logging 打开就好了

alter database add supplemental log data;

--==================情景3(drop表)============================================

drop table LOCATION;select * from LOCATION;flashback table LOCATION to before drop ;select * from LOCATION;create table tt1 as select * from LOCATION;drop table tt1;select * from user_recyclebin;purge recyclebin ;create table tt1 as select * from LOCATION;drop table tt1 purge;select * from user_recyclebin;

--=======================情景4(表的混合操作)=============================

create table tt1 as select * from LOCATION;alter table tt1 enable row movement;delete from  tt1;commit;select * from tt1;flashback table tt1 to timestamp sysdate-0.5/24/60;

--=================情景5(数据库级复杂操作)===============================
--很多表的数据被改动过
--删除过表，也增加过新表
--有些表结构也变过
--甚至存储过程等代码也更新过
--*******************************************************
--**附：打开Oracle数据库的Flashback功能(默认是关闭的！)**
--**需要先启动flashback功能,日志模式为archivelog模式： **
--*******************************************************

shutdown immediate; startup mount;  alter system set db_recovery_file_dest_size=4G;  alter system set db_recovery_file_dest='请自己决定一个目录如d:\oracle';  alter system set db_flashback_retention_target=7200;(注，请自己决定可回退的时长，单位是分钟，这里假设可回退5天)  ARCHIVE LOG LIST; alter database archivelog; select flashback_on from v$database;  alter database flashback on;  alter database open;

--=======================开始闪回Database====================================

sqlplus / as sysdbashutdown immediatestartup mount;flashback database to timestamp sysdate-20/24/60; alter database open read only;--alter database open resetlogs;

--==================验证闪回的数据/表结构/代码==============================

select * from LOCATION;select * from rtp_list;select * from t2;select * from user_tab_cols where table_name='R12';--查看f_str2tab函数，注释掉的好部分代码又恢复了！--验证确认正确后，操作如下：shutdown immediatestartup mount;alter database open resetlogs;--此情景很适合测试环境的反复利用！！！

--=======================删除无用的归档日志文件============================

rman target / RMAN> list archivelog all;RMAN> delete archivelog from time 'sysdate-10' until time 'sysdate-1';RMAN> delete archivelog until time 'sysdate-1';RMAN> list archivelog from time 'sysdate-10' until time 'sysdate-1';RMAN> list backup;RMAN> BACKUP DATABASE format='d:\%d_%u.bak';RMAN> RESTORE...;EXIT;ALTER system archive log current;select *--recid,name,deleted,statusfrom v$archived_log;