1.表闪回

1.1修改数据

  1. update  fixf_svr_xzh set ye=2000   where   zhid='131408260000000001';
  2. commit;
  3. select  to_char(sysdate,'yyyy-hh-dd hh24:mi:ss')   from dual;

1.2.查询修改之前数据

SELECT *
  FROM   fixf_svr_xzh AS 
  OF TIMESTAMP to_timestamp('2019-09-26 01:34:46', 'yyyy-mm-dd hh24:mi:ss')  
  where zhid='131408260000000001';

1.3.查看时间范围内事物

指定时间段


select versions_xid,versions_startscn,versions_starttime,versions_operation,
versions_endscn,versions_endtime
from fixf_svr_xzh  versions between timestamp 
to_timestamp('2019-10-01 03:12:20','yyyy-mm-dd hh24:mi:ss')
and to_timestamp('2019-10-01 03:13:20','yyyy-mm-dd hh24:mi:ss')
where zhid='131408260000000001' ;

1.4查询当前时间之后

select   versions_xid,versions_startscn,versions_starttime,versions_operation,versions_endscn,versions_endtime
  from   fixf_svr_xzh versions between timestamp systimestamp-INTERVAL '20' MINUTE and systimestamp
  where zhid='131408260000000001';

1.5根据scn查看表中数据已修改数据

select * from fixf_svr_xzh as of scn 8266885 where zhid='131408260000000001';

2查看闪回日志

注意此功能使用需要开起系统设置和用户权限

alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
grant execute on dbms_flashback to C##GUEST;
grant  select  any TRANSACTION  to C##GUEST;
grant   create  table  to C##GUEST;

2.1查看表时间范围的日志

SELECT DISTINCT xid, commit_scn
  FROM flashback_transaction_query t
 WHERE table_owner = 'C##GUEST'
       AND t.table_name LIKE '%'
       AND t.commit_timestamp >= systimestamp - INTERVAL '120' minute
 ORDER BY t.commit_scn;

2.2查询单个事务详细信息，包含undo sql

select * from flashback_transaction_query t where t.xid=hextoraw('0600190049260000');

3。闪回数据

3.1闪回表数据

update  fixf.fixf_svr_xzh set ye=2000   ;
commit;

记录时间闪回

select  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')   from dual;

查询数据是否符合要求

select * from fixf.fixf_svr_xzh as of timestamp to_timestamp('2019-10-01 03:13:20', 'yyyy-mm-dd hh24:mi:ss');

执行闪回

注意开启关闭行移动

alter table fixf.fixf_svr_xzh enable row movement;
flashback table fixf.fixf_svr_xzh to timestamp to_timestamp('2019-10-01 03:13:20', 'yyyy-mm-dd hh24:mi:ss');
alter table fixf.fixf_svr_xzh disable row movement;

4.1闪回单个事务


 BEGIN
  dbms_flashback.transaction_backout(numtxns => 1
                                    ,xids => sys.xid_array('0600190049260000')
                                    ,options => dbms_flashback.nonconflict_only) ;
END;
commit；

4.2查看闪回记录和报告

select * from dba_flashback_txn_state;
select * from dba_flashback_txn_report;

5.闪回数据归档

create flashback archive fda1 tablespace users retention 1 year;设置闪回归档表空间
alter flashback archive fda1 set default;设置默认
alter table C##GUEST.fixf_svr_xzh flashback archive;开起闪回归档保留，注意次步奏会无法drop表
alter table C##GUEST.fixf_svr_xzh no  flashback archive;取消闪回归档保留，此事才能drop表

fbda后台进程

select * from dba_flashback_archive;闪回数据归档信息
select * from dba_flashback_archive_ts;表空间信息
select * from dba_flashback_archive_tables;表信息

6.闪回删除和回收站

flashback table fx.t to before drop;
flashback table fx."BIN$kqS5Kebv+ongU2XmqMBdCA==$0" to before drop;
flashback table fx."BIN$kqS5Kebw+ongU2XmqMBdCA==$0" to before drop rename to ta;

6.1查看空闲空间

select * from  dba_free_space;

6.2手工回收

purge table|index
purge tablespace
purge recyclebin
purge dba_recyclebin

6.3不使用回收站

drop table t purge;
drop tablespace ts_name including contents;
drop user fx cascade;

6.4查看回收站:

show recyclebin;
select * from dba_recyclebin;
select * from user_recyclebin;

7.数据生命周期

示例

create table tv(id number,name varchar2(20),period for user_time);
create table tv2(id number,name varchar2(20),
user_time_start date invisible default sysdate,
user_time_end date invisible default sysdate+1/1440,
period for user_time(user_time_start,user_time_end));

INSERT INTO tv2(id,name) values(1,'a');
INSERT INTO tv2(id,name) values(2,'b');
commit;
---------------------------------------------
BEGIN
开起数据隐藏
  dbms_flashback_archive.enable_at_valid_time('CURRENT');--会话级
  关闭数据隐藏
    dbms_flashback_archive.disable_asof_valid_time;

    dbms_flashback_archive.enable_at_valid_time('ALL');--所有级别
END;
------------------------------------------------------------------

设置某个时间之前的数据查看不到

BEGIN
  dbms_flashback_archive.enable_at_valid_time('ASOF',to_timestamp('2019-09-16 21:27:09', 'yyyy-mm-dd hh24:mi:ss'));
END;

select * from tv2 as of period for user_time to_date('2019-09-16 21:27:09', 'yyyy-mm-dd hh24:mi:ss');

select * from tv2 versions period for user_time 
between to_date('2014-07-14', 'yyyy-mm-dd') and to_date('2014-07-16', 'yyyy-mm-dd');

8.闪回数据库

alter system set db_recovery_file_dest_size=200G;
alter system set db_recovery_file_dest='/u01/fra';
alter system set db_flashback_retention_target=2880;
alter database flashback on;
alter database open;
select flashback_on from v$database;

8.1根据scn闪回库

flashback database to scn 11781038;
alter database open read only;
alter database open resetlogs;

8.2闪回还原点

create restore point p1 guarantee flashback database;
flashback database to restore point p1;

9.pdb闪回

PDB中支持的闪回数据库方式：
1.还原点
2.scn
3.timestamp

PDB闪回有几个基本的前提条件：
1，enable local undo
2，enable archivelog mode
3，enable flashback database

alter session set container=pdb1;
create table lhr.test2 as select * from dual;
select current_scn from v$database; --4532218 
alter system switch logfile;
drop table lhr.test2;

alter pluggable database pdb1 CLOSE immediate;
flashback pluggable database pdb1 to scn 4532218;
alter pluggable database pdb1 open resetlogs;

select * from lhr.test2;