闪回查询、闪回表、闪回数据库(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/60
minus
select * from LOCATION;
insert into LOCATION 
select * from LOCATION as of timestamp sysdate-5/24/60
minus
select * 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/60
minus
select * 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 sysdba
shutdown immediate
startup 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 immediate
startup 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,status
from v$archived_log;