12c Database : Flashback data archives now optimized with compression option

Earlier to 12c, the Flashback data archive option is not used compression of FDA data(the changes happens to the table will be copied to some internal FDA tables, Daily a partition is created for the table and the daily wise changes to the data will be copied to the FDA tables like SYS_FBA*).

From 12c onwards, the FDA can be optimized to reduce the storage consumption by enabling the compression at row level (compression for OLTP)

Those who does not know about FDA, read this post.

Lets take a closer look. Note, the FDA is not supported in Pluggable database

Let's create two tablespaces and create FDA and then enable FDA on the table with two options optimize and no optimize and see the difference in the DDL of FDA tables that created to manage/store the DML changes happens to the table.

Tablespace creation

SQL> CREATE TABLESPACE FBA_1 DATAFILE 'FBA_1.DBF' SIZE 1G;

Tablespace created.

SQL> CREATE TABLESPACE FBA_2 DATAFILE 'FBA_2.DBF' SIZE 1G;

Tablespace created.

Create FDA

SQL> CREATE FLASHBACK ARCHIVE FBA1 TABLESPACE FBA_11 RETENTION 1 MONTH NO OPTIMIZE DATA;

Flashback archive created.

SQL> CREATE FLASHBACK ARCHIVE FBA2 TABLESPACE FBA_2 RETENTION 1 MONTH OPTIMIZE DATA;

Flashback archive created.

Create Table and enable FDA

SQL> CREATE TABLE T1 (ID NUMBER(19), DESCRIPTION VARCHAR2(42)) FLASHBACK ARCHIVE FBA1;

Table created.

SQL> CREATE TABLE T2 (ID NUMBER(19), DESCRIPTION VARCHAR2(42)) FLASHBACK ARCHIVE FBA2;

Table created.

Add some data to the tables, so that SYS_FBA* get created.

SQL> INSERT INTO T1 VALUES (88, 'Geek DBA');

1 row created.

SQL> commit;        

Commit complete.

SQL> DELETE FROM T1;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO T2 VALUES (83, 'Geek DBA');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DELETE FROM T2;

1 row deleted.

SQL> COMMIT;

Commit complete.

Check the syntax for the SYS_FBA* tables metadata and see the difference the tablespace that created with nooptimize data is not used any compression where in the other table used compression for OLTP.

SQL> select dbms_metadata.get_ddl('SYS_FBA_HIST_73535') from dual;

CREATE TABLE "SCOTT"."SYS_FBA_HIST_73535"
   (    "RID" VARCHAR2(4000 BYTE),
    "STARTSCN" NUMBER,
    "ENDSCN" NUMBER,
    "XID" RAW(8),
    "OPERATION" VARCHAR2(1 BYTE),
    "ID" NUMBER(19,0),
    "DESCRIPTION" VARCHAR2(42 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBA_1"
  PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBA_1" ) ;

CREATE TABLE "SCOTT"."SYS_FBA_HIST_73538"
   (    "RID" VARCHAR2(4000 BYTE),
    "STARTSCN" NUMBER,
    "ENDSCN" NUMBER,
    "XID" RAW(8),
    "OPERATION" VARCHAR2(1 BYTE),
    "ID" NUMBER(19,0),
    "DESCRIPTION" VARCHAR2(42 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
COMPRESS FOR OLTP
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBA_2"
  PARTITION BY RANGE ("ENDSCN")
(PARTITION "HIGH_PART"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
COMPRESS FOR OLTP LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "FBA_2" ) ;

Thanks for reading

Geek DBA