Here is a quick post concerning storage space requirements when using the Total Recall database feature. We did not notice this when we first set things up and were quite surprised when we looked a little closer at the space usage patterns of our flashback archives after a few weeks.
The default size for the initial extents of partitions was changed in 18.104.22.168 from 64kB to 8MB (details), propably with the intent that any table worth partitioning would usually be big enough to call for large extents anyway. But when you are using flashback archives, a new partition will be generated for you daily, no matter if you actually change anything in the base table (and generate fba data) or not. Multiply this by the number of tables you have enabled for total recall and this may easily add up to a significant amount of data.
This really would not be a very big issue if there FBAs would make use of deferred segment creation. But even though this is enabled at the instance level, the archives are implicitly created with “SEGMENT CREATION IMMEDIATE”. This is a look at the DDL of one of the underlying fba_hist tables
CREATE TABLE "GPM"."SYS_FBA_HIST_75880" ( "RID" VARCHAR2(4000 BYTE), "STARTSCN" NUMBER, "ENDSCN" NUMBER, "XID" RAW(8), "OPERATION" VARCHAR2(1 BYTE), "DTYPE" VARCHAR2(124 BYTE), "ID" NUMBER(19,0), "CODE" VARCHAR2(64 BYTE), "NAME" VARCHAR2(512 BYTE), [...] ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "FB_ARCHIVE" PARTITION BY RANGE ("ENDSCN") (PARTITION "PART_14188342" VALUES LESS THAN (14188342) 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 "FB_ARCHIVE" , ...
At this moment there is no way to change the partitioning parameters (to either make the initial extents smaller or use set segment creation to deferred) that are being used for flashback archives so the only chance to influence this is by altering the new (hidden) parameter file _partition_large_extents _before_ enabling total recall for a table.