How to create a Flashback Data Archive

Remember that tablespaces used for flashback data archive must use ASSM (Automatic Segment Space Managment)

Create a tablespace (here I am using Oracle Managed Files, no file specification necessary)

SQL> CREATE TABLESPACE LAST_MONTH;

Tablespace created.

SQL> CREATE TABLESPACE LAST_YEAR;

Tablespace created.

Next, create the flashback archive containers:

SQL> CREATE FLASHBACK ARCHIVE ONE_MONTH TABLESPACE LAST_MONTH RETENTION 1 MONTH;

FLASHBACK ARCHIVE CREATED.

SQL> CREATE FLASHBACK ARCHIVE ONE_YEAR TABLESPACE LAST_YEAR RETENTION 1 YEAR;

FLASHBACK ARCHIVE CREATED.


Prepare the table so it can use flashback archive:

SQL> ALTER TABLE SCOTT.CUSTOMERS ENABLE ROW MOVEMENT;

TABLE ALTERED.

Enable flashback archiving by assigning the table to the appropriate flashback archive container:

SQL> ALTER TABLE SCOTT.CUSTOMERS FLASHBACK ARCHIVE LAST_YEAR;

TABLE ALTERED.


To revers the operations:

SQL> ALTER TABLE CUSTOMERS NO FLASHBACK ARCHIVE;

TABLE ALTERED.

SQL> DROP FLASHBACK ARCHIVE ONE_YEAR;

FLASHBACK ARCHIVE DROPPED.

SQL> DROP FLASHBACK ARCHIVE ONE_MONTH;

FLASHBACK ARCHIVE DROPPED.

Useful views:

DBA_FLASHBACK_ARCHIVE

DBA_FLASHBACK_ARCHIVE_TABLES

DBA_FLASHBACK_ARCHIVE_TS