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!!!

About these ads

8 thoughts on “Reversing the effect of a TRUNCATE TABLE in Oracle 11gR2

  1. Hello LMC,
    i want to do your example, but i get this

    SQL> alter table mytab flashback archive enable row movement;
    alter table mytab flashback archive enable row movement
    *
    FEHLER in Zeile 1:
    ORA-01735: UngĀ³ltige Option ALTER TABLE
    i use 11.2.0.2
    have you an idea??
    best regards
    Markus

  2. can you explain why would row movement is required.
    alter table mytab flashback archive enable row movement

  3. You really make it seem so easy with your presentation but I in
    finding this topic to be really something that I think I would by no means understand.
    It sort of feels too complex and very extensive for me.
    I am looking forward to your next post, I’ll try to get the hold of it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s