UNDO_SQL in FLASHBACK_TRANSACTION_QUERY

When You read my post on Flashback and Materialized View You might think about other workaround. In FLASHBACK_TRANSACTION_QUERY View there is column with SQL Statement to Undo operation on table. Lets do simple test:

SQL> insert into a_fb values (1,1);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from a_fb;
1 row deleted.
SQL> commit;

And now see whats in FLASHBACK_TRANSACTION_QUERY.

SQL> select commit_scn, operation, row_id, undo_sql
  2  from FLASHBACK_TRANSACTION_QUERY
  3  where table_name = 'A_FB'
  4  order by commit_scn;
COMMIT_SCN OPERATION                        ROW_ID                              
---------- -------------------------------- -------------------                 
UNDO_SQL                                                                        
--------------------------------------------------------------------------------
    641488 INSERT                           AAAM2dAAEAAAAG9AAA                  
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';                  
                                                                                
    641571 DELETE                           AAAM2dAAEAAAAG9AAA                  
insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1'); 

To run above query You need SELECT ANY TRANSACTION privilege
So now instead of issuing FLASHBACK TABLE A_FB to SCN 641400 You may run statements from UNDO_SQL:

insert into "BARUT"."A_FB"("ID","TEXT") values ('1','1');
delete from "BARUT"."A_FB" where ROWID = 'AAAM2dAAEAAAAG9AAA';

But it will not work! Because when you insert new row usually it will get new row id

SQL> select rowid, id from a_fb;

ROWID                      ID                                                   
------------------ ----------                                                   
AAAM2dAAEAAAAG9AAB          1

So when issuing Delete statement you must map old rowid to that new one. When you do Flashback on Table oracle manages it internally. But when you use UNDO_SQL you must take care for all details. So be careful with that…

Cheers, Paweł

Related articles:

• Flashback Table and Materialized View – not working together
• Rolling Back the DDL
• More on Flashback