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

1

 

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
             )
/

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. – Great stuff! I have been using the

    CREATE RESTORE POINT MYDATA;
    COMMIT;

    alter table xxx enable row movement;
    commit;

    — then
    Flashback table xxx to restore point mydata;

    way to go…learn something today!