How does the SYS_FBA _ prefix table serve the FlashbackDataArchive

The before-image in undo is affected by system load and other factors, and its retention time is short. Sometimes it cannot fully meet the flashbackquery, flashbackversionquery, and other flashbacks.

The before-image in undo is affected by system load and other factors, and its retention time is short. Sometimes it cannot fully meet flashback query, flashback version query, and other flashbacks.

In undo, before-image is affected by system load and other factors, and its retention time is short. Sometimes, it cannot fully meet the functional requirements of flashback query, flashback version query, and other flash-back queries for earlier data, to solve this problem, the introduction of flashback data archive regularly archives before images from undo to archive tables.
If flashback data archive is created in the database, the backend Process FBDA (Flashback Data Archiver Process) is started, and alert. log also has the following output:
Sun May 17 13:35:18 2015
Starting background process FBDA
Sun May 17 13:35:18 2015
FBDA started with pid = 35, OS id = 12257378

You can enable the flashback archive function when creating a table, or enable the flashback archive function by using Alter table... flashback archive... after creating a table.
When the data block in the table is modified, before image marks the undo block while writing it to the undo block, indicating that the undo block needs to be archived to the flashback data archive, this archiving process is completed by the FBDA process. Before archiving is completed, this undo block cannot be reused by other transactions. The process of archiving undo blocks to flashback data archive is asynchronous, so the impact on transaction performance is negligible. FBDA scans the archived undo blocks every five minutes, write it to the flashback data archive, and mark the undo block as reusable. If the system requires a large modification, the scanning interval is less than 5 minutes, which is controlled by Oracle.

During the technical implementation of flashback data archive, the table prefixed with SYS_FBT _ plays a significant role. The following experiment shows how

/// // Part 1. SYS_FBA _ TABLE basic introduction ////////////////

### The database already has a flashback archive named FBA0516_1. The quota value is 300 MB, which is stored in the TS0512_1 tablespace. No historical data is stored in any table in FBA0516_1.
SYS @ tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE;

OWNER_NAME FLASHBACK _ FLASHBACK_ARCHIVE # RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------------------------------------------------------------------------------------------------------------
SYS FBA0516_1 1 16-MAY-15 11.46.01.000000000 AM 16-MAY-15 11.46.01.000000000 AM

SYS @ tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;

FLASHBACK _ FLASHBACK_ARCHIVE # TABLESPACE_NAME QUOTA_IN_MB
--------------------------------------------------------------------------------------------------
FBA0516_1 1 TS0512_1 300

SYS @ tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE_TABLES;

No rows selected

### Create a test table
Create table t0516_5 (id number, c2 varchar2 (3) flashback archive fba0516_1;

Col object_name format a20
Set linesize 100
Select object_name, created, object_id from dba_objects where object_name = 't0516 _ 5 ';
OBJECT_NAME CREATED OBJECT_ID
-----------------------------------------------
T0516_5 20150516 20:45:54 36945

--- The archive table corresponding to T0516_5 is sys_fba_hist_1_37, but we haven't found sys_fba_hist_1_37 in dba_tables.
Col OWNER_NAME format a10
Set numwidth 4
Col FLASHBACK_ARCHIVE_NAME format a10
Col create_time format a35
Col last_purge_time format a35
Set linesize 140
Select * from DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK _ ARCHIVE_TABLE_NAME STATUS
--------------------------------------------------------------------------------------------------------------------
T0516_5 SCOTT FBA0516_1 sys_fba_hist_000045 ENABLED

Select owner, table_name, partitioned from dba_tables where table_name like '% 100 ';

No rows selected

According to the official statement, the backend process FBDA checks every five minutes whether a new archive table is to be created. After more than 10 minutes, the sys_fba_hist_37 table is not created, perform some DML operations and then observe
--- Insert data
Insert into t0516_5 values (1, 'aaa ');
Insert into t0516_5 values (2, 'bbb ');
Insert into t0516_5 values (3, 'ccc ');
Commit;

SCOTT @ tstdb1-SQL> select sysdate from dual;

SYSDATE
-----------------
20:47:06 20150516

--- The table starting with SYS_FBA is not queried immediately. It is not created until 20:50:19, 20150516, and the time between SYS_FBA and the last insert operation is <5 minutes.
SCOTT @ tstdb1-SQL> select owner, table_name, partitioned from dba_tables where table_name like '% 100 ';

No rows selected

.... Wait for a moment

SCOTT @ tstdb1-SQL> select owner, table_name, partitioned from dba_tables where table_name like '% 100 ';