闪回技术演示oracle

演示环境：

SQL> column product for a50
SQL> column version for a15
SQL> column status for a20

SQL> select * from product_component_version where product like 'Oracle%';--当前数据库的版本号

PRODUCT                                            VERSION
-------------------------------------------------- ---------------
STATUS
--------------------
Oracle Database 11g Enterprise Edition             11.2.0.4.0
64bit Production

1.闪回查询

SQL> select *from scott.emp as of timestamp to_date('2019-06-12 16:14:36','yyyymm-dd hh24:mi:ss') where empno=7369;

EMPNO ENAME	JOB	MGR HIREDATE	SAL COMM
DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------
-
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800
20

2 闪回版本查询（Oracle Flashback Version Query）
使用Oracle闪回版本查询可以检索一个给定的时间间隔内特定行的不同版本，当执行
Commit时，就会创建一个row版本。
使用SELECT的Versions Between语句指定闪回版本查询，语法为：
Versions {Between {SCN | Timestamp} start And end }
SQL> column versions_starttime for a30
SQL> column versions_endtime for a30
SQL> select versions_startscn, versions_starttime,versions_endscn, versions_endtime,versions_xid,versions_operation,ename,sal from scott.emp  versions between timestamp to_date('2019-08-27 16:14:36','yyyy-mm-dd hh24:mi:ss')  and to_date('2019-08-27 16:40:10','yyyy-mm-dd hh24:mi:ss') where empno=7369;

VERSIONS_STARTSCN VERSIONS_STARTTIME	VERSIONS_ENDSCN
VERSIONS_ENDTIME	VERSIONS_XID	V ENAME	SAL

----------------- ------------------------------ --------------- ----------------------------
-- ---------------- - ---------- ----------
1284173 27-AUG-19 04.39.16 PM
02000E0063040000 U SMITH 2388.79

1284166 27-AUG-19 04.38.58 PM	1284173 27-AUG-19 04.39.16 PM
0A0008008D030000 U SMITH	1990.66

 

1284161 27-AUG-19 04.38.55 PM 04001F0098030000 U SMITH 1658.88

1284166 27-AUG-19 04.38.58 PM

1284143 27-AUG-19 04.38.37 PM 1284161 27-AUG-17 04.38.55 PM
0200210062040000 U SMITH 1382.4

1281680 27-AUG-19 04.15.16 PM	1284143 27-AUG-19 04.38.37 PM
01001F0080030000 U SMITH	1152

1281680 27-AUG-19 04.15.16 PM
SMITH 800
6 rows selected.