Flashback Archive data for keep history of all changes (FDA)

Flashback Data Archive provides the automated ability to track and store all transactional changes to a table over its lifetime without having to build this intelligence into your application.Flashback Data Archive uses a background process fbda to capture data asynchronously.

Creating a Flashback Data Archive (lets call it FBDA)

·  Create a new tablespace or use existing tablespace – tablespace needs to be ASSM

·  Specify the FBDA as the default (optional)

·  Assign a quota for the FBDA (optional)

·  Assign a retention period for the FBDA

·  Retention period integer denoting days,months or years

·  Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off.    

SQL> create tablespace historical_data_01;

Tablespace created.

----------------------------------------------------------------------

SQL>  create flashback archive fba1 tablespace historical_data_01 retention 2 DAY;

Flashback archive created.

----------------------------------------------------------------------

SQL> select flashback_archive_name,to_char(create_time,'dd-mon-yyyy') created,retention_in_days,status from dba_flashback_archive;

                FLASHBACK_ARCHIVE_NAME            CREATED                       RETENTION_IN_DAYS  STATUS

    ---------------------- --------         -----------------          ----------------------     -------------------------------------   ------------------

                          FBA1                                      28-nov-2015                                                                2          

----------------------------------------------------------------------

SQL> grant flashback archive on fba1 to sys;

SQL> create table vahid(id number,name varchar2(50));

SQL>  insert into vahid(id,name) values(1,'Alex');

1 row created.

SQL> commit;

 ----------------------------------------------------------------------

SQL> alter table vahid flashback archive fba1;

----------------------------------------------------------------------

SQL> select table_name ,flashback_archive_name,archive_table_name,status from user_flashback_archive_tables;

            TABLE_NAME FLASHBACK_ARCHIVE_NAME          ARCHIVE_TABLE_NAME          STATUS

    ----------  ----------------------  -------------------   --------

      VAHID                FBA1                      SYS_FBA_HIST_221804      ENABLED

----------------------------------------------------------------------

 SQL>insert into vahid(id,name) values(2,'Anjel');

  commit;

 ----------------------------------------------------------------------

SQL>  select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') date_system from dual;

            DATE_SYSTEM

    -------------------------------

            28-11-2015 14:45:51

----------------------------------------------------------------------

 SQL>update vahid set name='vahid' where ID=2;

  commit;

 ----------------------------------------------------------------------

  SQL> select * from vahid;

            ID         NAME

             1            Alex

             2           vahid

 ----------------------------------------------------------------------

SQL> select * from vahid as of timestamp to_timestamp('28-11-2015 14:45:00','dd-mm-yyyy hh24:mi:ss');

            ID         NAME

             1          Alex

              2        Anjel

Now you can see history of changing record with ID=2.

 ----------------------------------------------------------------------

For dropping flashback archive  :

SQL> drop flashback archive fba1;

SQL> drop tablespace historical_data_01;