潛水珽的異想世界

通常用於檢索一條記錄的所有版本，倒退單獨的事務或者倒退從指定時間以來對特定表的所有變化
Flashback Query的所有形式取決於UNDO表表空間，關於UDNO表空間請參考：Oracle 回滾(ROLLBACK)和撤銷(UNDO)

--1.閃回查詢(Flashback Query)語法

SELECT <column_name_list>

FROM <table_name>

AS OF <SCN>                            --使用as of scn

[WHERE <filter_conditions>]

[GROUP BY <unaggregated columns>]

[HAVING <group_filter>]

[ORDER BY <column_positions_or_name>]

SELECT <column_name_list>
FROM <table_name>
AS OF <TIMESTAMP>                      --使用as of timestamp
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]

--2.演示閃回查詢
a.演示使用as of timestamp來進行閃回查詢
create table tb1 as select empno,ename,job,deptno from scott.emp where 1=0;

insert into tb1
select empno,ename,job,deptno
from scott.emp where empno in(7369,7499,7521,7566);

commit;

select * from tb1;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20 

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

TO_CHAR(SYSDATE,‘YY‘
-------------------
2010-10-25 17:26:08

delete from tb1 where job=‘SALESMAN‘;

commit;

select * from tb1;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7566 JONES      MANAGER           20   

select * from tb1 as of timestamp
to_timestamp(‘2010-10-25 17:26:08‘,‘yyyy-mm-dd hh24:mi:ss‘);

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20   

select * from tb1 as of timestamp
to_timestamp(‘2010-10-25 17:26:08‘,‘yyyy-mm-dd hh24:mi:ss‘)
minus select * from tb1;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30

b.演示使用as of scn來進行閃回查詢

flasher@ORCL11G> select current_scn from v$database;

CURRENT_SCN
-----------
    2032782

flasher@ORCL11G> select * from tb1;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 Henry      CLERK             20
      7566 JONES      MANAGER           20

flasher@ORCL11G> delete from tb1 where empno=7369;

flasher@ORCL11G> commit;

flasher@ORCL11G> select * from tb1 as of scn 2032782;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 Henry      CLERK             20
      7566 JONES      MANAGER           20     

由以上可知，通過閃回查詢獲得所需的記錄信息，然後來構造新的DML語句並實施其操作來保證數據的完整性。
 

ref:
https://www.796t.com/content/1521735669.html