Total Recal flashback data archives

Total Recal flashback data archives is yet another cool feature of Oracle.

It's a recovery solution and the ability to track changes made on a table.
This is how you set it up and use it.

First you grant flashback archive administer to an user.
SQL>grant flashback archive administer to gda001;

Now we create a tablespace and a retention period of 1 year

SQL> create tablespace flashtest datafile '+DATA' size 50M;
SQL> create flashback archive DEFAULT flash1 tablespace flashtest

Now we create a test table

SQL> create table obj1 as select * from dba_objects;

Now we enable archive data on the table.

SQL>alter table obj1  flashback archive;
Table altered.

To see which tables area enabled for flashback archive

SQL>select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
          f rom USER_FLASHBACK_ARCHIVE_tables;
TABLE_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME             STATUS
------------------------------ ------------------------------ ------------------------------ -------------
OBJ1                           FLASH1                           SYS_FBA_HIST_72380             ENABLED

To see if compression is enabled

SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_72380             HIGH_PART                      ENABLED
Now i'm going to delete some records

SQL> select count(*) from obj1;
  COUNT(*)
----------
     68894

SQl> delete from obj1 where rownum < 68000
SQL>commit;

SQL> select count(*) from obj1;
  COUNT(*)
----------
       904

wait 5 minutes and then

SQL> delete from obj1 where rownum < 800;
799 rows deleted.

SQL> select count(*) from obj1;
  COUNT(*)
----------
       105

To fake that we are using the flashback archive instead of undo i shutdown and start the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  680607744 bytes
Fixed Size                  2231472 bytes
Variable Size             499123024 bytes
Database Buffers          171966464 bytes
Redo Buffers                7286784 bytes
Database mounted.
Database opened.
SQL> select count(*) from obj1
          as of timestamp
        to_timestamp ('2012-07-31 15:27:00','YYYY-MM-DD HH24:MI:SS');
  COUNT(*)
----------
     68994
now to find the one with the 105 records

SQL>  select count(*) from obj1
           as of timestamp
          to_timestamp ('2012-07-31 15:32:00','YYYY-MM-DD HH24:MI:SS');
  COUNT(*)
----------
       105
Now you can off course disable flashback archive if you want
You will lose all historical data when you do this.

SQL> alter table obj2 no flashback archive;
Table altered.
The same happens when you drop of purge the pashback archive data.

SQL>  Drop flashback archive flash1;
   Flashback archive dropped.

SQL>Alter flashback archive flash1 PURGE ALL;
When on the table flashback aechive is enable some ddl statements fail

SQL> drop table OBJ1;
drop table OBJ1
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

but the same happens when you want to add a column or want to rename the table.

Enjoy this nice feature