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

Lucas Jellema 1
0 0
Read Time:2 Minute, 3 Second

 

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

 

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

One thought on “Using Flashback Query in Oracle SQL to spot changes and compare records

  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!

Comments are closed.

Next Post

(Dutch) AMIS kennissessie Scrum

Datum: dinsdag 3 maart  2009 Tijd: 16:30 tot 21:00, incl. diner Locatie: AMIS, Edisonbaan 15 in Nieuwegein Doelgroep: Deze sessie is interessant voor zowel developers, projectmanagers, consultants als sales medewerkers. Coördinator: Robbrecht van Amerongen Scrum, Just enough, just in time… Scrum is een methodiek die ons in staat stelt om snel kwalitatief […]
Scrum process
%d bloggers like this: