A friend in need: Flashback Query

"It is sayd, that at the nede the frende is knowen."

- William Caxton: "Sonnes of Aymon" (1489)

"I say, that when you screw up, Flashback Query is a friend indeed!"

- Morten Braten: "SQLs of Oracle" (2012)

The Flashback Query feature of Oracle (10g) can be really helpful when you run that UPDATE or DELETE statement and then, after the COMMIT, you discover that you made a mistake!

Wouldn't it be nice to be able to look back and see what the data looked like before you messed things up? With a Flashback Query you can do just that:


-- see the data as it was 10 minutes ago
select *
from emp
as of timestamp systimestamp - interval '10' minute
where empno = 7369


Or you can see a history of changes:


-- history of all changes last 20 minutes
select empno, ename, sal, job,
  versions_operation,
  versions_starttime,
  versions_endtime,
  versions_xid
from emp
versions between timestamp systimestamp - interval '20' minute and systimestamp
where empno = 7369
order by versions_endtime nulls last


I've found this useful several times lately, so I'm adding it to the blog as a quick reminder, as I always struggle to remember that funky interval syntax thingy.