Unspoiling the demo – Flashback Table and Flashback Query

Lucas Jellema

The Whoops experience again. While preparing demos for OOW 2006, I did one update – or rather one commit – too many. I had assigned all employees to the same department. I want them back to where they belong. Note that in this case the Employees are not a metaphore for some large enterprise scale table with customers or orders. It is really about table EMP. For me that is the enterprise level table as virtually all of my demos involve EMP.

Well, here I was, yelling at myself for not clearly thinking before acting (I even sounded a bit like my mother I realize), when I suddenly came to my senses and remembered the flashback features of my dear database.....

At first I tried the simples approach: flashback the entire table to a moment in time when it was still in proper prime condition:

flashback table emp to timestamp sysdate - 1
/

ORA-08189: cannot flashback the table because row movement is not enabled

Unfortunately, I had not enabled row movement on my table. Well, enabling row movement is just a simple statement that can be executed even after the disaster has struck, so I could work my way out of the pickle:

  1  ALTER TABLE emp
2* ENABLE ROW MOVEMENT
SQL> /

Table altered.

SQL> flashback table emp to timestamp sysdate - 1
2 /

Flashback complete.

another, somewhat more involved and therefore more flexible approach is an update using flashback query:

update emp e1 
set deptno = ( select deptno
from emp as of timestamp sysdate -1 e2
where e2.empno = e1.empno
)
/

This allows we to pinpoint the rows to update as well as the columns to manipulate.

Resources

Oracle 10gR2 Table Flashback on PSOURG: http://www.psoug.org/reference/tab_flashback.html

Oracle’s Disappearing Act: Flashback Query

Next Post

On the false sense of security with PL/SQL based implementation of business rules - and what to do about it

Facebook0TwitterLinkedinBuilding applications in our corner of the world typically revolves around databases. The most important bit about an application is not the application, but rather the data it creates and processes that gets stored in the database. And even before security and performance, the integrity of that data is our […]