Unspoiling the demo – Flashback Table and Flashback Query

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