Reversing the effect of a TRUNCATE TABLE in Oracle 11gR2

Today I'll share with you a way to reverse the effect of a TRUNCATE TABLE without any complex recovery procedures. I'm assuming the schema used has DBA privileges, just to avoid switching from user to user during the example.
During the example I'll also show you a new 11gR2 feature that enables you to do DDL on tables that have UNDO data going to a Flashback Data Archive (FDA).

Here's the outline:

1) Create a dedicated FDA
2) Create a trial table having it's undo data going to the new FDA
3) Insert data
4) Take note of both SCN and SYSDATE
5) Truncate the trial table (and see that it takes more time than usual in versions previous to 11gR2)
6) Try to FLASHBACK the table without success
7) Using FLASHBACK QUERY with success and reverse the effect of the TRUNCATE: no sweat!

Here's the code:

SQL> show user
USER is "LMC"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
(...)
SQL> CREATE FLASHBACK ARCHIVE ando TABLESPACE users QUOTA 500m RETENTION 1 year;
Flashback archive created.
SQL> create table mytab (n number, x varchar2(90), d date);
Table created.
SQL> alter table mytab flashback archive ando;
Table altered.
SQL> alter table mytab flashback archive enable row movement
Table altered.
SQL> insert into mytab values (1,'Monsters of Folk',sysdate);
1 row created.
SQL> insert into mytab values (2,'The Frames',sysdate-1/24);
1 row created.
SQL> commit;
Commit complete.
SQL> select CURRENT_SCN from v$database;
CURRENT_SCN
-----------
    1614140
SQL> select * from mytab;
         N X                                   D
---------- ----------------------------------- ---------
         1 Monsters of Folk                    19-OCT-09
         2 The Frames                          19-OCT-09
         3 Boys Like Girls                     18-OCT-09
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
20091019 19:11:47
SQL> set timing on
SQL> truncate table mytab;
Table truncated.
Elapsed: 00:00:17.03

It took seventeen seconds to truncate. It's due to the 11gR2 algorithm that enables DDL support in Flashback Data Archives.

SQL> flashback table mytab to scn 1615151;
flashback table mytab to scn 1615151
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

I was expecting that, no problem. You can't flashback table through DDL
Now let's try going to the past just to fetch the info...

SQL> select * from mytab as of timestamp TO_TIMESTAMP('20091019 19:11:47','yyyymmdd hh24:mi:ss');
         N X                                   D
---------- ----------------------------------- ---------
         1 Monsters of Folk                    19-OCT-09
         2 The Frames                          19-OCT-09
         3 Boys Like Girls                     18-OCT-09
SQL> insert into mytab
  2  select * from mytab as of timestamp TO_TIMESTAMP('20091019 19:11:47','yyyymmdd hh24:mi:ss');
3 rows created.
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> select * from mytab;
         N X                                   D
---------- ----------------------------------- ---------
         1 Monsters of Folk                    19-OCT-09
         2 The Frames                          19-OCT-09
         3 Boys Like Girls                     18-OCT-09

SQL> -- My data is back!!!