0 
0
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
)
/
Related posts:
Collect and share interesting bookmarks 
Another JDeveloper extension: Force Delete utility for ViewObjects (ADF Business Components pka BC4J) 
Providing default-values for find-pages in JHeadstart applications (UIX/BC4J/Struts) Paris for the Designer Installing Tomcat5.5 and integrating it with Apache 1.3
— 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!