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