What is in the 11g Flashback Data Archive?

Directly after the creation of the flashback archive there are no objects in it.

SYS AS SYSDBA @ orcl SQL> SELECT segment_name, segment_type FROM dba_segments WHERE tablespace_name='FBA_5_YEAR_01'; 

no rows selected

The next step is for the DBA to apply some changes to the data in the table:

SYS AS SYSDBA @ orcl SQL> UPDATE lutz.t_d SET col2=col2+1234;
SYS AS SYSDBA @ orcl SQL> COMMIT;

128 rows updated.

Commit complete.

In the data dictionary, information about an ?archive table? can now be seen.  This is the table which is used to hold the historical data:

SYS AS SYSDBA @ orcl SQL> SELECT * FROM dba_flashback_archive_tables; 

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
---------- ----------- --------------------- ------------------
T_D        LUTZ       MY_5_YEAR_ARCHIVE      SYS_FBA_HIST_60849

SYS AS SYSDBA @ orcl SQL> desc lutz.SYS_FBA_HIST_60849

 Name         Null?    Type
 --------------------- ----------------
 RID                    VARCHAR2(4000) -- this is the rowid
 STARTSCN               NUMBER
 ENDSCN                 NUMBER
 XID                    RAW(8)
 OPERATION              VARCHAR2(1)
 COL1                   NUMBER        -- here we find the column data
 COL2                   NUMBER        -- here we find the column data

Directly after a DML operation, the history data in the archive table of the flashback archive is not seen. It takes some time until the undo information is moved by FBDA from the undo tablespace to the history table. Archive table and history table are used synonymously here. In the data dictionary it is archive_table and its name is sys_fba_hist_xxx.

SYS AS SYSDBA @ orcl SQL> SELECT * FROM  lutz.SYS_FBA_HIST_60849; 

no rows selected

After some time, the information about the DMLs can be seen in the flashback archive.

SYS AS SYSDBA @ orcl SQL> SELECT * FROM lutz.SYS_FBA_HIST_60849; 

RID               STARTSCN     ENDSCN XID              O       COL1       COL2
----------------- ---------    -----  ---------------- -      ------      ----
AAAO4YAAEAAAAAWAAB             9505692                            2       1120
AAAO4YAAEAAAAAXAA+  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA/  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA0  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA1  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA2  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA3  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA4  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA5  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXAA6  950440     950569 040006008B020000 I          2       1120
 . . .
 . . .
AAAO4YAAEAAAAAXABs  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABt  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABu  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABv  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABw  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABx  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABy  950440     950569 040006008B020000 I          2       1120
AAAO4YAAEAAAAAXABz  950440     950569 040006008B020000 I          2       1120

128 rows selected.

There are also more objects in the tablespace contents of the flashback archive:

SYS AS SYSDBA @ orcl SQL> SELECT segment_name, segment_type FROM dba_segments WHERE tablespace_name='FBA_5_YEAR_01'; 

SEGMENT_NAME                SEGMENT_TYPE
------------------------    ----------------------
SYS_FBA_DDL_COLMAP_60849    TABLE
SYS_FBA_HIST_60849          TABLE PARTITION
SYS_FBA_TCRV_60849          TABLE
SYS_FBA_TCRV_IDX_60849      INDEX

Note that the history table is partitioned. It has the same structure as the tracked table, plus a few additional columns needed for the transaction information. The data in the history table is automatically compressed with a new table compression algorithm. Oracle 11g can now use table compression for normal DML operations. Before 11g, table compression could only be used with bulk load operations such as DIRECT INSERTs. 

SYS AS SYSDBA @ orcl SQL>  SELECT table_name, table_owner, partition_name, 
                                   compression, compress_for 
                            FROM DBA_TAB_PARTITIONS
                             WHERE tablespace_name='FBA_5_YEAR_01'; 

TABLE_NAME        TABLE_OWNER    PARTITION_NAME    COMPRESS COMPRESS_FOR
----------------  ------------   ---------------   -------- ------------------
SYS_FBA_HIST_60849  LUTZ         HIGH_PART         ENABLED  FOR ALL OPERATIONS

Here is a look at the other table, available in the flashback archive tablespace. This one contains not only the history of the INSERTs but also the history of the UPDATEs:

SYS AS SYSDBA @ orcl SQL> desc lutz.SYS_FBA_TCRV_60849;

 Name         Null?    Type
 ------------ -------- --------------------------------------------
 RID          VARCHAR2(4000)
 STARTSCN     NUMBER
 ENDSCN       NUMBER
 XID          RAW(8)
 OP           VARCHAR2(1)

SYS AS SYSDBA @ orcl SQL> SELECT * FROM  lutz.SYS_FBA_TCRV_60849; 

RID                              STARTSCN     ENDSCN XID              O
------------------------------ ---------- ---------- ---------------- -
AAAO4YAAEAAAAAWAAB                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA+                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA+                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA/                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA/                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA0                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA0                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA1                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA1                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA2                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA2                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA3                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA3                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA4                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXAA4                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXAA5                 950569            09000D00AF020000 U
. . .
. . .
. . .

AAAO4YAAEAAAAAXABr                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABr                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABs                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABs                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABt                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABt                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABu                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABu                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABv                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABv                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABw                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABw                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABx                 950569            09000D00AF020000 U
AAAO4YAAEAAAAAXABx                 950440     950569 040006008B020000 I
AAAO4YAAEAAAAAXABy                 950569            09000D00AF020000 U

255 rows selected.

Applications and users do not need to query the history data directly from the history table.  In fact, normal SQL can be used with flashback functionalities. This means that the history data is used seamlessly.

Here is what the execution plan for a flashback query on the flashback data archive looks like:

SYS AS SYSDBA @ orcl SQL> set autotrace trace explain
SYS AS SYSDBA @ orcl SQL> SELECT * FROM lutz.t_d AS OF TIMESTAMP sysdate-1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3381664885

| Id  | Operation                 | Name               | Rows  | Bytes | Cost (% CPU)|
Time     | Pstart| Pstop |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     2 |    52 |    13 (8)|
00:00:01 |       |       |
|   1 |  VIEW                     |                    |     2 |    52 |    13 (8)|
00:00:01 |       |       |
|   2 |   UNION-ALL               |                    |       |       |   
|           |       |       |
|*  3 |    FILTER                 |                    |       |       |   
|           |       |       |
|   4 |     PARTITION RANGE SINGLE|                    |     1 |    17 |     3 (0)|
00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_60849 |     1 |    17 |     3 (0)|
00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                    |       |       |   
 |           |       |       |
|*  7 |     HASH JOIN OUTER       |                    |     1 |    34 |    10 ( 10)|
00:00:01 |       |       |
|*  8 |      TABLE ACCESS FULL    | T_D                |     6 |    42 |     6 (0)|
00:00:01 |       |       |
|   9 |      VIEW                 |                    |   128 |  3456 |     3 (0)|
00:00:01 |       |       |
|* 10 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_60849 |   128 |  3456 |     3 (0)|
00:00:01 |       |       |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("TIMESTAMP_TO_SCN"(SYSDATE@!-1)<979993)
5 - filter("ENDSCN">"TIMESTAMP_TO_SCN"(SYSDATE@!-1) AND ("STARTSCN" IS NULL O R
          "STARTSCN"<="TIMESTAMP_TO_SCN"(SYSDATE@!-1)) AND "ENDSCN"<=979993)
6 - filter("F"."STARTSCN"<="TIMESTAMP_TO_SCN"(SYSDATE@!-1) OR "F"."STARTSCN" IS NULL)
7 - access("T".ROWID=CHARTOROWID("F"."RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL)
10 - filter(("ENDSCN" IS NULL OR "ENDSCN">979993) AND ("STARTSCN"<979993 OR "S TARTSCN" IS NULL))