oracle闪回学习二

1.闪回时间点查询

1.查询hr.employess在2014年1月30号下午1点40分37秒所有的行
SQL> select * from hr.employees as of timestamp to_timestamp('2014-01-30 13:40:37','YYYY-MM-DD HH24:MI:SS');


2.查询100号员工在2014年1月30号下午1点40分37年时候的薪水
SQL> select salary from hr.employees as of timestamp to_timestamp('2014-01-30 13:40:37','YYYY-MM-DD HH24:MI:SS') where employee_id = 100; 


    SALARY
----------
     24000


3.查询100号员工5分钟前的薪水
修改employee_id的工资为100000元
SQL> update hr.employees set salary = 100000 where employee_id = 100;


1 row updated.


查看五分钟之前员工号为100的工资
SQL> select salary from hr.employees as of timestamp (systimestamp - interval '5' minute) where employee_id = 100;  


    SALARY
----------
     24000


查看员工号100现在的工资
SQL> select salary from hr.employees where employee_id = 100;


    SALARY
----------
    100000


SQL> commit；


Commit complete.

 
4.查看100号员工在SCN为1154993时的工种


SQL> select current_scn from v$database;


CURRENT_SCN
-----------
    1154993


SQL> select job_id from hr.employees as of scn 1154993 where employee_id = 100;


JOB_ID
----------
AD_PRES 


5.将10分钟前的hr.employees表和1小时前的hr.departments表以department_id为条件关联(join)
SQL> select 
e.last_name,
d.department_name 
from 
hr.employees as of timestamp (systimestamp - interval '10' minute) e,
hr.departments as of timestamp (systimestamp - interval '1' hour) d
where e.department_id = d.department_id; 


LAST_NAME  DEPARTMENT_NAME
------------------------- ------------------------------
Whalen  Administration
Fay  Marketing
Hartstein  Marketing
Tobias  Purchasing
Colmenares  Purchasing
Baida  Purchasing
Raphaely  Purchasing
Khoo  Purchasing
Himuro  Purchasing
Mavris  Human Resources
Feeney  Shipping


···························································
------------------------- ------------------------------
Urman  Finance
Chen  Finance
Faviet  Finance
Sciarra  Finance
Greenberg  Finance
Gietz  Accounting
Higgins  Accounting


106 rows selected.




6.将100号员工的薪资修改到40分钟前的值
SQL> select salary from hr.employees where employee_id = 100;


    SALARY
----------
    100000


SQL> update hr.employees 
set salary = (select salary from hr.employees as of timestamp
(systimestamp - interval '40' minute)
where employee_id = 100)
where employee_id = 100;


1 row updated.


SQL> commit;


Commit complete.


SQL> select salary from hr.employees where employee_id = 100;


    SALARY
----------
     24000