重庆思庄Oracle、Redhat认证学习论坛

标题: 闪回查询与闪回版本查询 [打印本页]

作者: denglj 时间: 2022-12-8 15:58
标题: 闪回查询与闪回版本查询

文档课题：测试闪回查询与闪回版本查询.

应用场景：

a、恢复丢失的数据或撤消不正确的已提交的更改.如commit错误的dml事务，此时便可通过闪回查询将数据找回.

b、将当前数据与过去某一时刻的数据进行比较.

实际操作：

1、闪回查询

SQL>select employee_id,first_name,last_name,hire_date,salary from employees03;

EMPLOYEE_IDFIRST_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_IDFIRST_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 rowsselected.

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

SALARY

----------

5800

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

1 rowupdated.

SQL>commit;

Commitcomplete.

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 oftimestamp语句便可查出5分钟前或1天前的数据.

2、闪回版本查询

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

1 rowupdated.

SQL>commit;

Commitcomplete.

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

SALARY

----------

9600

8500

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

1 rowupdated.

SQL>commit;

Commitcomplete.

SQL>select salary from employees03 versions between scn minvalue and maxvalue wherefirst_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 andmaxvalue where first_name='Kevin'

START_DATE END_DATE SALARY

-------------------------------------------------- ----------

02-DEC-2207.42.03 PM 11520

02-DEC-2207.40.24 PM 02-DEC-22 07.42.03 PM 9600

02-DEC-22 07.40.24PM 8500

说明：薪水11520对应版本END_DATE的NULL值表示目前查询的为现有版本.薪水8500对应版本START_DATE的NULL值表示该版本薪水超过undoretention保留时间.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)