Flashback Data Archive

Create flashback data archive

CREATE FLASHBACK ARCHIVE DEFAULT test_archive1

TABLESPACE example

QUOTA 1 M

RETENTION 1 DAY;

CREATE FLASHBACK ARCHIVE test_archive2

TABLESPACE example

QUOTA 1 M

RETENTION 1 DAY;  

CREATE FLASHBACK ARCHIVE DEFAULT fla1 

TABLESPACE tbs1

QUOTA 10G RETENTION 1 YEAR;

CREATE FLASHBACK ARCHIVE fla2 

TABLESPACE tbs2 

RETENTION 2 YEAR;

To modify retention

ALTER FLASHBACK ARCHIVE test_archive1

MODIFY RETENTION 1 MONTH;

To specific tracking for the table on default data archive

ALTER TABLE oe.customers FLASHBACK ARCHIVE;

To specific tracking for the table on specific data archive

ALTER TABLE oe.orders

FLASHBACK ARCHIVE test_archive2;

Drop flashback data archive

DROP FLASHBACK ARCHIVE test_archive2;

Syntax 

ALTER FLASHBACK ARCHIVE flashback_archive

  { SET DEFAULT

  | { ADD | MODIFY } TABLESPACE tablespace [flashback_archive_quota]

  | REMOVE TABLESPACE tablespace_name

  | MODIFY RETENTION flashback_archive_retention

  | PURGE { ALL | BEFORE { SCN expr | TIMESTAMP expr } }

  | [NO] OPTIMIZE DATA

  };

Altering a Flashback Data Archive

Make Flashback Data Archive fla1 the default Flashback Data Archive:


ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;

To Flashback Data Archive fla1, add up to 5 G of tablespace tbs3:


ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;

To Flashback Data Archive fla1, add as much of tablespace tbs4 as needed:


ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs4;

Change the maximum space that Flashback Data Archive fla1 can use in tablespace tbs3 to 20 G:


ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs3 QUOTA 20G;

Allow Flashback Data Archive fla1 to use as much of tablespace tbs1 as needed:


ALTER FLASHBACK ARCHIVE fla1 MODIFY TABLESPACE tbs1;

Change the retention time for Flashback Data Archive fla1 to two years:


ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;

Remove tablespace tbs2 from Flashback Data Archive fla1:


ALTER FLASHBACK ARCHIVE fla1 REMOVE TABLESPACE tbs2;


(Tablespace tbs2 is not dropped.)

Purge all historical data from Flashback Data Archive fla1:


ALTER FLASHBACK ARCHIVE fla1 PURGE ALL;

Purge all historical data older than one day from Flashback Data Archive fla1:


ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

Purge all historical data older than SCN 728969 from Flashback Data Archive fla1:


ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE SCN 728969;

Dropping a Flashback Data Archive

DROP FLASHBACK ARCHIVE fla1;

Specifying the Default Flashback Data Archive

ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;

CREATE FLASHBACK ARCHIVE DEFAULT fla2 

TABLESPACE tbs1

QUOTA 10G RETENTION 1 YEAR;

CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),

JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;

Enabling and Disabling Flashback Data Archive

Create table employee and store the historical data in the default Flashback Data Archive:


CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),

  JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;

Create table employee and store the historical data in the Flashback Data Archive fla1:


CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),

 JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE fla1;

Enable Flashback Data Archive for the table employee and store the historical data in the default Flashback Data Archive:


ALTER TABLE employee FLASHBACK ARCHIVE;

Enable Flashback Data Archive for the table employee and store the historical data in the Flashback Data Archive fla1:


ALTER TABLE employee FLASHBACK ARCHIVE fla1;

Disable Flashback Data Archive for the table employee:


ALTER TABLE employee NO FLASHBACK ARCHIVE;