To create a flashback archive you need the FLASHBACK ARCHIVE ADMINISTER priviledge. With this priviledge you can craete a flashback archive :
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_tbs QUOTA 100M RETENTION 5 YEARS;
To register a table with a flashback archive you need the FLASHBACK ARCHIVE privilege.
SQL> GRANT FLASHBACK ARCHIVE on fda to <user>;With this priviledge you can reister a table with a specific flashback archive :
SQL> ALTER TABLE my_table FALSHBACK ARCHIVE fda;
With flashback archive registered for a specific table you can query the table in the past
SQL> SELECT * FROM my_table AS OF TIMESTAMP to_timestamp( '05/12/2001 10:41:00', 'DD/MM/YYYY HH24:MI:SS');
note : To make Flashback Data Archive fda the default Flashback Data Archive:
SQL> ALTER FLASHBACK ARCHIVE fda SET DEFAULT;note : to modify the retention policy
SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 2 YEARS;note : to purge data older then a specific time
SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' DAY);note : to drop an archive
SQL> DROP FLASHBACK ARCHIVE fda;note : common DDL requests are supported by flashback archive but for complete ones it is necessary to disassociate the table before to procceed any modification. Total recall provides the DISASSOCIATED_FBA & REASSOCIATE_FBA procedure of the DBMS_FLASHBACK_ARCHIVE package for this purpose.
note :
- Automatic undo management must be enabled.
- The tablespace in which flashback data archive is created must have Automatic Segment Space Management activated.
- You can not drop a table that is managed by total recall but you can truncate it.
