From version 11g Oracle has come with new enhancement – Flashback Data Archive also known as Oracle Total Recall.

With the “Oracle Total Recall” option, Oracle database 11g has been specifically enhanced to track history with minimal performance impact and to store historical data in compressed form to minimize storage requirements, completely transparent to applications, easy to setup. It is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables.You specify retention duration for each flashback data archive (could be # of years).

Normally traditional flashback table has its own limitation as it cannot flashback table on which any DDL performed including truncate. Look at example given below:

13:40:10 SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
 YES
13:40:38 SQL> create table test as select * from dba_users;
Table created.
13:41:20 SQL> select count(0) from test;
COUNT(0)
----------
 10
13:41:29 SQL> insert into test select * from test;
10 rows created.
13:42:00 SQL> commit;
Commit complete.
13:42:03 SQL> select count (0) from test;
COUNT(0)
----------
 20
13:45:06 SQL> alter table test enable row movement;
Table altered.
13:45:20 SQL> FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS');
Flashback complete.
13:45:30 SQL> select count(0) from test;
COUNT(0)
----------
 10
13:46:08 SQL> truncate table test;
Table truncated.
13:46:28 SQL> select count(0) from test;
COUNT(0)
----------
 0
13:46:40 SQL> FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE test
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
14:04:26 SQL> select count(*) from test as of timestamp TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS');
select count(*) from test as of timestamp TO_TIMESTAMP('2014-08-21 13:41:29', 'YYYY-MM-DD HH24:MI:SS')
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Here we can conclude that we can not flashback table which has been truncated. We can overcome with this limitation using Oracle Total Recall. Please find below steps to use this feature :

14:31:33 SQL> create flashback archive flash_test tablespace FATBS retention 10 year;
Flashback archive created.
14:32:15 SQL> alter table test flashback archive flash_test;
Table altered.
14:32:56 SQL> insert into test select * from dba_users;
10 rows created.
14:33:24 SQL> commit;
Commit complete.
14:33:28 SQL> select count(0) from test;
COUNT(0)
----------
 20
14:33:37 SQL> truncate table test;
Table truncated.
14:34:05 SQL> FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE test TO TIMESTAMP TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS')
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
14:34:25 SQL> select count(*) from test as of timestamp TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
 20
14:34:56 SQL> insert into test select * from test as of timestamp TO_TIMESTAMP('2014-08-21 14:33:36', 'YYYY-MM-DD HH24:MI:SS');
20 rows created.
14:35:59 SQL> commit;
Commit complete.
14:36:05 SQL> select count(0) from test;
COUNT(0)
----------
 20

I got my data back even though table was truncated. 🙂

Flashback archive restrictions:

• If flashback archive is enabled some DDL operations can cause ORA-55610 – Invalid DDL statement on history-tracked table like drop table, alter table etc.
• You can’t enable Flashback Data Archive for temporary, external tables & tables containing long datatype

Reference Note : 11g feature: Flashback Data Archive Guide. (Doc ID 470199.1)

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath