Using Flashback Query in Oracle SQL to spot changes and compare records

 

Using Flashback Query is really simple. Leveraging Flashback Query in applications has become a more viable option with the advent of the Table Archive in Oracle 11g R1 – as with that feature a database administrator can instruct the database to collect and retain historic data for a specific table (rather than all redo log files for all tables heaped together).

This article shows some simple scripts with Flashback Query that demonstrate how easy we can spot changes in records.

Change some records in table EMP:

update emp
set    sal = sal * 1.1
where  job = 'CLERK'
/

commit;

Retrieve the current contents in EMP

select empno
,      ename
,      job
,      deptno
,      sal
from   emp
/

and select the contents of EMP as it was until 5 minutes ago:

select empno
,      ename
,      job
,      deptno
,      sal
from   emp as of timestamp systimestamp - 5/24/60
/

Retrieve the changed records by selecting the difference (MINUS) between the current record set and the historic set:

select empno
,      ename
,      job
,      deptno
,      sal
from   emp
minus
select empno
,      ename
,      job
,      deptno
,      sal
from   emp as of timestamp systimestamp - 5/24/60

List the changed records side by side with the original records

with historic_emps as
( select empno
  ,      ename
  ,      job
  ,      deptno
  ,      sal
  from   emp as of timestamp systimestamp - 1/24
)
,    modified_emps as
( select empno
  ,      ename
  ,      job
  ,      deptno
  ,      sal
  from   emp
  minus
  select empno
  ,      ename
  ,      job
  ,      deptno
  ,      sal
  from   historic_emps
)
select *
from   modified_emps m
union all
select *
from   historic_emps h
order
by     1
/

Analyze the exact value changes in the JOB and SAL columns

update emp
set    job = 'CAESAR'
,      sal = sal + 500
where  ename = 'KING'
and    job = 'PRESIDENT'
/

commit
/


rem what are the exact changes in job and salary?

with historic_emps as
( select empno
  ,      ename
  ,      job
  ,      deptno
  ,      sal
  from   emp as of timestamp systimestamp - 1/24
)
,    modified_emps as
( select empno
  ,      ename
  ,      job
  ,      deptno
  ,      sal
  from   emp
  minus
  select empno
  ,      ename
  ,      job
  ,      deptno
  ,      sal
  from   historic_emps
)
select m.empno
,      m.ename
,      m.deptno
,      m.job current_job
,      case h.job
       when m.job then null
       else h.job end previous_job
,      m.sal new_sal
,      h.sal old_sal
,      (m.sal - h.sal) salary_change
from   modified_emps m
       join
       historic_emps h
       on (m.empno = h.empno)
order
by     1
/

Undo all changes made within the last hour:

update emp
set    (sal, job, deptno) =
             (select sal, job, deptno
              from   emp as of timestamp systimestamp - 1/24 history
              where  history.empno = emp.empno
             )
/