Unspoiling the demo – Flashback Table and Flashback Query

0

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<br />/<br /><br />ORA-08189: cannot flashback the table because row movement is not enabled <br />

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<br />  2* ENABLE ROW MOVEMENT<br />SQL&gt; /<br /><br />Table altered.<br /><br />SQL&gt; flashback table emp to timestamp sysdate - 1<br />  2  /<br /><br />Flashback complete. <br />

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

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

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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.