DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, April 8, 2015

FLASHBACK DATA ARCHIVE


                 FLASHBACK DATA ARCHIVE 

The new background process,FDA(Flashback Data Archive), tracks all changes made to the table and stores it in a file in a compressed and partitioned format. However, you cannot use this feature with clustered, temporary, nested, remote or external tables and long or nested columns. It tracks all transactional changes made to specific tables for the specific timeinterval. In the following scenario, you will see the configuration and usage of this feature in detailed examples.


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
FLOWERS   READ WRITE

SQL> create user larry identified by ellison;

User created.

SQL> grant connect,resource,flashback archive administer to larry;

Grant succeeded.

SQL> create tablespace tbs_darch datafile '/u01/app/oracle/darch01.dbf' size 15m;

Tablespace created.

SQL> create flashback archive fl_archive tablespace tbs_darch retention year;

Flashback archive created.

SQL> create table balaji_fl_archive(id number,name varchar2(25),addr varchar(20),phone number(10));

Table created.

SQL> insert into balaji_fl_archive values(10292,'Balaji Ramakrishnan','Chennai',9998887776);

1 row created.

SQL> insert into balaji_fl_archive values(10291,'Vazhavandan Lingadurai','Madurai',6777888999);

1 row created.

SQL> insert into  balaji_fl_archive values(10200,'Pushparaj','Salem',9997778886);

1 row created.

SQL> insert into balaji_fl_archive values(10201,'Sathishkumar','Delhi',7778889967);

1 row created.

SQL> insert into balaji_fl_archive values(10202,'Praveen','Calicut',8887779996);

1 row created.

SQL> commit;                       

Commit complete.

SQL> select * from balaji_fl_archive;
        ID NAME                      ADDR        PHONE
---------- -------------------- ----- -------------------- -
     10292 Balaji Ramakrishnan       Chennai      9998887776
     10291 Vazhavandan Lingadurai    Madurai      6777888999
     10200 Pushparaj                   Salem         9997778886
     10201 Sathishkumar               Delhi          7778889967
     10202 Praveen                     Calicut        8887779996

SQL> alter table balaji_fl_archive flashback archive fl_archive;

Table altered.

SQL> select to_char(sysdate,'ddmmyyyy hh24:mi:ss') bdate from dual;

BDATE
-----------------
27022015 16:20:58

SQL> delete from balaji_fl_archive;

5 rows deleted.

SQL> commit;                       

Commit complete.

SQL> select * from balaji_fl_archive;

no rows selected

SQL> select * from balaji_fl_archive as of timestamp to_timestamp('27022015 16:20:58','ddmmyyyy hh24:mi:ss');       
        ID NAME                      ADDR                      PHONE
---------- ------------------------- -------------------- ----------
     10292 Balaji Ramakrishnan       Chennai              9998887776
     10291 Vazhavandan Lingadurai    Madurai              6777888999
     10200 Pushparaj                 Salem                9997778886
     10201 Sathishkumar              Delhi                 777888996
     10202 Praveen                   Calicut              8887779996

    This feature doesn’t look to the UNDO tablespace for the HISTORICAL information on the rows for the specific time.

1 comment:

Prima Source said...

A very interesting article. The insights are really helpful and informative. Thanks for posting. Outsourcing Global Delivery|IT Offshoring and OutsourcingTop IT Companies In India