闪回查询 -- 查询过去某一个时刻的状态 ( 查询的是已经提交的数据 )
闪回版本查询 -- 查询一个时间段内做那些操作
闪回事务查询 -- 核心是事务的相反语句（undo SQL），可以还原整个事务也可以撤销事务部分数据
示例：
当前scott.emp表中scott的工资
SQL> select ename ,sal from scott.emp;
ENAME SAL
---------- ----------
SMITH 800
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
ALLEN 1600
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000 #这是scott的工资
KING 5000
FORD 3000
修改Scott的工资
update scott.emp set sal=0 where ename ='SCOTT';
commit;
select ename ,sal from scott.emp where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
要查出scott原来的工资多少，然后恢复原来的工资
查询过去某个时刻的语法：
select 字段名,.....
from 表名
AS OF TIMESTAMP <T1> 或者 scn
where 条件
查1分钟前scott的工资
SQL> select ename ,sal from scott.emp as of timestamp(sysdate-5/1440) where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 0
查10分钟前scott的工资
SQL> select ename ,sal from scott.emp as of timestamp(sysdate-10/1440)
where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
如何表示过去的某个时间
1分钟前： sysdate -1/1440
5分钟前： sysdate -5/1440
1小时前： sysdate -1/24
1天前 sysdate -1
查具体的过去的某个时间
select ename ,sal from scott.emp as of timestamp to_timestamp('2017-9-25 17:00:00','yyyy-mm-dd hh24:mi:ss') ;
恢复
update scott.emp set sal=(select sal from scott.emp as of timestamp(sysdate-10/1440) where ename ='SCOTT') where ename ='SCOTT';
commit;
SQL> select ename ,sal from scott.emp where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
恢复删除的数据
delete from scott.emp where rownum <=4;
commit;
原来14条，现在10条，查出到底哪4条数据被删除了
select * from scott.emp as of timestamp(sysdate -5/1440) minus select * from scott.emp;
还原
insert into scott.emp (select * from scott.emp as of timestamp(sysdate -5/1440) minus select * from scott.emp);
建立一张视图v_15, 可以查看scott.emp表15分钟的数据
create or replace view v_15 as select * from scott.emp as of timestamp(system - 15/1440);
伪列：
versions_xid 事务编号
versions_startscn 起始的SCN
versions_endscn 结束SCN号 如果有值，证明这记录已非当前记录
versions_starttime 起始时间
versions_endtime 结束时间
versions_operation 操作类型
VERSIONS_OPERATION说明：
I 插入
D 删除
U 更新
语法：
SELECT versions_xid, to_char(versions_starttime ,'yyyy-mm-dd hh24:mi:ss'),VERSIONS_OPERATION ,字段名
from 表名
VERSIONS BETWEEN TIMESTAMP <t1> and <t2>
WHERE 条件
示例：
建立scott.e表， 和scott.emp一样
create table scott.e as select * from scott.emp;
查看当前时间
select to_char(sysdate ,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-09-25 00:35:19
执行3个事务
update scott.e set sal=0 where empno =7788;
commit;
update scott.e set sal=1 where empno =7788;
commit;
delete from scott.e where empno =7788;
commit;
select empno,ename ,sal from scott.e where empno =7788;
no rows selected
SQL> select to_char(sysdate ,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-09-25 00:36:51
查出scott.e表在该时间段内发生了哪些操作
select versions_xid, to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') ,versions_operation ,ename ,sal from scott.e VERSIONS BETWEEN TIMESTAMP to_timestamp('2017-09-25 00:35:19','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2017-09-25 00:36:51','yyyy-mm-dd hh24:mi:ss') where empno=7788;
VERSIONS_XID TO_CHAR(VERSIONS_ST V ENAME SAL
---------------- ------------------- - ---------- ----------
03000D0045030000 2017-09-25 00:35:53 D SCOTT 1
090012003D030000 2017-09-25 00:35:53 U SCOTT 1
0A00110073020000 2017-09-25 00:35:48 U SCOTT 0
SCOTT 3000
select ename ,sal from scott.emp as of timestamp to_timestamp('2017-09-25 00:35:48','yyyy-mm-dd hh24:mi:ss') where ename ='SCOTT';
ENAME SAL
---------- ----------
SCOTT 3000
闪回版本查询提供了两个参数minvalue、 maxvalue
select versions_xid, to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') ,versions_operation ,ename ,sal from scott.e VERSIONS BETWEEN TIMESTAMP minvalue and maxvalue where empno=7788;
VERSIONS_XID TO_CHAR(VERSIONS_ST V ENAME SAL
---------------- ------------------- - ---------- ----------
03000D0045030000 2017-09-25 00:35:53 D SCOTT 1
090012003D030000 2017-09-25 00:35:53 U SCOTT 1
0A00110073020000 2017-09-25 00:35:48 U SCOTT 0
SCOTT 3000
如果要找回记录：
insert into scott.emp (select * from scott.emp as of timestamp to_timestamp('2017-09-25 00:35:48','yyyy-mm-dd hh24:mi:ss') where empno =7788);
commit;
必须先开启补全日志
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns; （必须是有主键的表）
示例：
用户执行一个事务
update scott.emp set sal=sal+1000;
commit;
操作完发现不应该修改scott的sal，需要将scott还原，其余的不变
（先找出是哪个事务 -- 版本查询 --
再找出事务ID -- 找出UNDO SQL -- 执行恢复）
通过版本查询，查出时间和事务编号
select versions_xid, to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') ,versions_operation ,ename ,sal from scott.emp VERSIONS BETWEEN TIMESTAMP minvalue and maxvalue where empno=7788;
VERSIONS_XID TO_CHAR(VERSIONS_ST V ENAME SAL
---------------- ------------------- - ---------- ----------
07001A0068020000 2017-09-25 00:58:34 U SCOTT 4000
SCOTT 3000
事务编号 07001A0068020000
通过事务编号去找 FLASHBACK_TRANSACTION_QUERY
select OPERATION,TABLE_NAME,ROW_ID ,UNDO_SQL from FLASHBACK_TRANSACTION_QUERY where XID='07001A0068020000';
select UNDO_SQL from FLASHBACK_TRANSACTION_QUERY where XID='07001A0068020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '2975' where ROWID = 'AAASR/AAEAAAAI9AAD';
update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAASR/AAEAAAAI9AAC';
update "SCOTT"."EMP" set "SAL" = '1600' where ROWID = 'AAASR/AAEAAAAI9AAB';
update "SCOTT"."EMP" set "SAL" = '800' where ROWID = 'AAASR/AAEAAAAI9AAA';
update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAASR/AAEAAAAI8AAN';
update "SCOTT"."EMP" set "SAL" = '3000' where ROWID = 'AAASR/AAEAAAAI8AAM';
update "SCOTT"."EMP" set "SAL" = '950' where ROWID = 'AAASR/AAEAAAAI8AAL';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAASR/AAEAAAAI8AAK';
update "SCOTT"."EMP" set "SAL" = '1500' where ROWID = 'AAASR/AAEAAAAI8AAJ';
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAASR/AAEAAAAI8AAI';
update "SCOTT"."EMP" set "SAL" = '3000' where ROWID = 'AAASR/AAEAAAAI8AAH';
update "SCOTT"."EMP" set "SAL" = '2450' where ROWID = 'AAASR/AAEAAAAI8AAG';
update "SCOTT"."EMP" set "SAL" = '2850' where ROWID = 'AAASR/AAEAAAAI8AAF';
update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAASR/AAEAAAAI8AAE';
SQL> select rowid ,ename ,sal from scott.emp where ename = 'SCOTT';
ROWID ENAME SAL
------------------ ---------- ----------
AAASR/AAEAAAAI8AAH SCOTT 4000
begin
update "SCOTT"."EMP" set "SAL" = '3000' where ROWID = 'AAASR/AAEAAAAI8AAH';
commit;
end;
/
闪回查询只能 找出undo-sql 还需要人工去执行语句.
参考手册：
Advanced Application Developer's Guide -- 12 Using Oracle Flashback Technology
Using Oracle Flashback Version Query
