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<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> /<br /><br />Table altered.<br /><br />SQL> 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.
Oracle 10gR2 Table Flashback on PSOURG: http://www.psoug.org/reference/tab_flashback.html
- Oracle's Disappearing Act: Flashback Query
- Publishing Data Manipulation as an RSS Feed – using Oracle MOD_PLSQL and Flashback
- Create logging data in the same table
- Cost Based Optimizing through time travel? – the value of meta-data for enabling Query Rewrite
- Find the number of descendant nodes in a SQL query – how many employees work (indirectly) for me? And what do they earn on average?