测试闪回查询与闪回版本

文档课题:测试闪回查询与闪回版本.
应用场景:
a、恢复丢失的数据或撤消不正确的已提交的更改.如commit错误的dml事务,此时便可通过闪回查询将数据找回.
b、将当前数据与过去某一时刻的数据进行比较.
实际操作:
1、闪回查询
SQL> select employee_id,first_name,last_name,hire_date,salary from employees03;

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY
----------- -------------------- --------------- ----------------------------------- ----------
100 Steven King 17-JUN-11 24000
101 Neena Kochhar 21-SEP-09 17000
102 Lex De Haan 13-JAN-09 17000
103 Alexander Hunold 03-JAN-14 9000
104 Bruce Ernst 21-MAY-15 6000
107 Diana Lorentz 07-FEB-15 4200
124 Kevin Mourgos 16-NOV-15 5800
141 Trenna Rajs 17-OCT-11 3500
142 Curtis Davies 29-JAN-13 3100
143 Randall Matos 15-MAR-14 2600
144 Peter Vargas 09-JUL-14 2500

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY
----------- -------------------- --------------- ----------------------------------- ----------
149 Eleni Zlotkey 29-JAN-16 10500
174 Ellen Abel 11-MAY-12 11000
176 Jonathon Taylor 24-MAR-14 8600
178 Kimberely Grant 24-MAY-15 7000
200 Jennifer Whalen 17-SEP-11 4860
201 Michael Hartstein 17-FEB-12 13000
202 Pat Fay 17-AUG-13 6000
205 Shelley Higgins 07-JUN-10 12008
206 William Gietz 07-JUN-10 12008

20 rows selected.
SQL> select salary from employees03 where first_name='Kevin';

SALARY
----------
5800

SQL> update employees03 set salary=8500 where first_name='Kevin';

1 row updated.

SQL> commit;

Commit complete.

SQL> select salary from employees03 where first_name='Kevin';

SALARY
----------
8500

SQL> select salary from employees03 as of timestamp (systimestamp-interval '5' minute) where first_name='Kevin';

SALARY
----------
5800

SQL> select salary from employees03 as of timestamp (systimestamp-interval '1440' minute) where first_name='Kevin';

SALARY
----------
5800

说明:如上使用as of timestamp语句便可查出5分钟前或1天前的数据.
2、闪回版本查询
SQL> update employees03 set salary=9600 where first_name='Kevin';

1 row updated.

SQL> commit;

Commit complete.

SQL> select salary from employees03 versions between scn minvalue and maxvalue where first_name='Kevin';

SALARY
----------
9600
8500

SQL> update employees03 set salary=salary*1.2 where first_name='Kevin';

1 row updated.

SQL> commit;

Commit complete.

SQL> select salary from employees03 versions between scn minvalue and maxvalue where first_name='Kevin';

SALARY
----------
11520
9600
8500
注意:执行过dml后需commit才能看到相应的修改记录.
SQL> select versions_starttime "START_DATE",versions_endtime "END_DATE",salary from employees03 versions between scn minvalue and maxvalue where first_name='Kevin'

START_DATE END_DATE SALARY
------------------------- ------------------------- ----------
02-DEC-22 07.42.03 PM 11520
02-DEC-22 07.40.24 PM 02-DEC-22 07.42.03 PM 9600
02-DEC-22 07.40.24 PM 8500
说明:薪水11520对应版本END_DATE的NULL值表示目前查询的为现有版本.薪水8500对应版本START_DATE的NULL值表示该版本薪水超过undo retention保留时间.