Oracle Flashback Data Archive (Total Recall)

Starting on Oracle 11g, it’s possible to keep every transaction made to the table and keep it as long as you want. Before 11g, in order to get a before image of any row, either we were getting it from archived redo log files (if they are kept) using Log Miner, or by writing a trigger to save the data in another log table.
But now, using Flashback Data Archive feature, a new background process, FBDA (Flashback Data Archive) tracks all changes made to the table and stores it in a file in a compressed and partitioned format. However, you can’t use this feature with clustered, temporary, nested, remote or external tables and LONG or nested columns

It tracks all transactional changes made to specific tables for the specific time interval. To use Flashback Data Archive feature the user needs to have the FLASHBACK ARCHIVE ADMINISTER system privilege. Moreover, the FLASHBACK ARCHIVE object privilege should be granted to the user to enable historical data tracking. In the following scenario we show you the configuration and usage of this feature in detailed examples

--Create a new user and grant him the required privileges:
create user usr identified by usr;
grant connect, resource, flashback archive administer to usr;

-- Create a new separate tablespace for data archive
CREATE TABLESPACE tbs_arch DATAFILE 'c:\flashback_archive.dbf' size 10m;

-- Create flashback archive on this tablespace using CREATE FLASHBACK ARCHIVE command as follows:
create flashback archive fl_archive tablespace tbs_arch retention 1 year;
 
With above command we’ve created a Flashback Archive named FL_ARCHIVE which resides in the tablespace TBS_ARCH and holds information for 1 year.
It means that we can use any Flashback Query which contains 1 year historical information regarding the table that assigned to this flashback archive

-- Now, create a table, insert one row and assign it to this flashback archive:
create table tbl_fl_archive (id number, name varchar2(20));
insert into tbl_fl_archive values(1,'Flashback Archive');
commit;
select * from tbl_fl_archive;
        ID NAME
---------- --------------------
         1 Flashback Archive
 
alter table tbl_fl_archive flashback archive fl_archive;


The historical change on the table TBL_FL_ARCHIVE is now will be written to the flashback archive named FL_ARCHIVE.

-- To test it, delete all rows and use Flashback Query on that table. Remember, it will not look for the UNDO data, it will look to the flashback archive file for the changes
select to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate from dual;
DDATE
-----------------
13022010 12:46:49
 
delete from tbl_fl_archive;
commit;
 
select * from tbl_fl_archive;
no rows selected
 
select * from tbl_fl_archive as of timestamp to_timestamp('13022010 12:46:49','ddmmyyyy hh24:mi:ss');
        ID NAME
---------- --------------------
         1 Flashback Archive

 
You should know that this feature doesn't look to the UNDO tablespace for the historical information on the rows for the specific time.


Flashback Features in Oracle 11g

Flashback Queries
Principal uses of flashback query :
- Repair bad data
- Collect and review recent data values over time

To query a table:
SELECT * FROM department AS OF TIMESTAMP TO_TIMESTAMP('03-MAY-2008 09:30:00');

To observe new records added today
CREATE TABLE changes_today AS
  SELECT * FROM employees
  MINUS
  SELECT * FROM employees AS OF TIMESTAMP TRUNC(SYSDATE);



Session Level Flashback
This can be used to observe data from several queries at a point in time using PL/SQL.
It requires execute privilege on dbms_flashback.

dbms_flashback.enable_at_time('22-NOV-2008 11:00:00AM');
SELECT ... FROM ... ;
SELECT ... FROM ... ; -- No DML or DDL allowed
SELECT ... FROM ... ;
dbms_flashback.disable;


If only a date is specified, time element defaults to 12 midnight

NOTES=
* sys can use SQL level but not session level flashback mode..
* Flashback is enabled at nearest SCN to the specified time rounded down to a 3 second interval
    - SCNs are mapped to times only once every 3 seconds
    - It's better to know SCN to flashback more accurately
* Does not work across a database bounce, Unless _IN_MEMORY_UNDO is set to false


Flashback Row and Transaction History
Shows row version data plus :
—Start and end times of the version (SCNs and timestamps)
—Transaction ID for every version of the row during the specified period

SELECT empno,sal,versions_starttime st,versions_xid XID
  FROM empf VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('01-MAY-08 05.24.02') AND TO_TIMESTAMP('01-MAY-08 05.35.02');
  WHERE empno = 7766;


Returns the salary for each transaction affecting the row, as follows:
EMPNO SAL  ST                 XID
----- ---- ------------------ -----------------
7766  8000 01-MAY-08 05.32.38 06001F0014170000
7766  7120 01-MAY-08 05.28.45 04002C00151B0000
7766  6300 01-MAY-08 05.26.14 0200290017370000


Flashback Transaction History - Scenario
Build a table with some data
CREATE TABLE empf
( empno NUMBER PRIMARY KEY ,
  ename VARCHAR2(16) ,
  sal number);

Sleep for a short time
INSERT INTO empf VALUES(7950, 'BROWN', 3000);
INSERT INTO empf VALUES(8888, 'GREEN', 4000);
INSERT INTO empf VALUES(1111, 'WHITE', 1000);
COMMIT;

SELECT * FROM EMPF;
EMPNO ENAME     SAL
----- --------- -----
 7950 BROWN     3000
 8888 GREEN     4000
 1111 WHITE     1000


Bad transaction correctly deletes a row, incorrectly updates the other
DELETE FROM empf WHERE empno = 7950;
UPDATE empf SET sal = sal + 3000 WHERE empno = 8888;
COMMIT;
EMPNO ENAME     SAL
----- --------- -----
 8888 GREEN     6000
 1111 WHITE     1000


New transaction updates a remaining row with new values
UPDATE empf SET sal = sal + 400 WHERE empno = 8888;
UPDATE empf SET sal = sal + 250 WHERE empno = 8888;
COMMIT;
EMPNO ENAME     SAL
----- --------- -----
 8888 GREEN     6650
 1111 WHITE     1000


Finding the Errant Transaction
The DBA decides that there has been an error and interrogates the versions of the rows (row history) for transaction information
SELECT versions_xid XID,
       versions_startscn START_SCN,
       versions_endscn END_SCN,
       versions_operation OPERATION,
       ename,
       sal,
       empno
FROM empf VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;


XID              START_SCN END_SCN   O ENAME  SAL   EMPNO
---------------- --------- --------- - ------ ----- ------
05002500BE000000    428380
          U BROWN   6650   8888
05001800BE000000
   428372    428380 U BROWN   6000   8888
05001800BE000000
   428372           D GREEN   4000   7950
04001600BE000000
   428365           I WHITE   1000   1111
04001600BE000000
   428365    428372 I BROWN   3000   8888
04001600BE000000
   428365    428372 I GREEN   4000   7950
Top row is the final transaction, second row is the error

Auditing the Transaction
Obtain the UNDO of the original SQL for the entire bad transaction (requires SELECT ANY TRANSACTION system privilege):
SELECT xid, undo_sql
FROM flashback_transaction_query
WHERE xid = '05001800BE000000';

XID               UNDO_SQL
---------------- -----------------------------------------
05001800BE000000 update "SCOTT"."EMPF" set "SAL" = '3000'
                 where ROWID = 'AAAMUDAAEAAAAIXAAB';
05001800BE000000 insert into
                 "SCOTT"."EMPF"("EMPNO","ENAME","SAL")
                 values ('7950','GREEN','4000');

Note = Searches on flashback_transaction_query can take some time


Flashback Table
Enables fast recovery of a table to a previous point in time (SCN or timestamp), the table has an exclusive DML lock while it is being restored.
To use it, the FLASHBACK ANY TABLE system privilege or FLASHBACK object privilege on table is needed. You also need to have SELECT, INSERT, UPDATE, DELETE privileges on the table. Finally, the table must have row movement enabled (ROWIDs are not preserved)

This feature automatically restores all dependent objects:
— Indexes dropped since flashback point will be synchronized with flashback table
— Indexes created after the flashback point will be dropped
— Statistics are not flashed back

Flashback occurs only if constraints are not violated
—Referential integrity constraints are maintained across all tables. A violation causes a rollback of the flashback statement

Data in the original table is not lost after a flashback
—You can later revert to the original state

Using Flashback Table
1. Use flashback row and transaction history to find position for flashback
Record current SCN at time of flashback if a reversal of the operation is required. This is found in v$database.current_scn or via dbms_flashback.get_system_change_number

2. Perform the flashback table operation
FLASHBACK TABLE t1 TO SCN 12345;
or
FLASHBACK TABLE t1 TO TIMESTAMP TO_TIMESTAMP('2008-12-01 12:05:00') ENABLE TRIGGERS;

Triggers are not enabled by default during a flashback operation

On executing FLASHBACK TABLE, a global temporary table called sys_temp_fbt is created in the user schema, the rows are inserted using INSERT APPEND. The rows are removed when session is terminated but the table is not dropped
The sys_temp_fbt tracks ROWIDs of affected rows:
Name           Type
-------------- ------------
SCHEMA         VARCHAR2(32)
OBJECT_NAME    VARCHAR2(32)
OBJECT#        NUMBER
RID            ROWID
ACTION         CHAR(1)


If you are curious, you can get the metadata for sys_temp_fbt:
SELECT DBMS_METADATA.GET_DDL('TABLE','SYS_TEMP_FBT') FROM DUAL;


Flashback Drop and the Recycle Bin
Dropping a table places the table in the recycle bin
—Table is renamed (and any associated objects) and space is marked as reusable
—Makes it possible to recover a table at a later time using
FLASHBACK TABLE ... TO BEFORE DROP
—No need to enable row movement

Objects are purged using the PURGE statement or automatically by Oracle when there is space pressure in their tablespace
Purging is performed on a first-in, first-out basis
Dropping a tablespace, or a user does not place any objects in the bin
Indexes, constraints and triggers of dropped tables are also held in recycle bin

Querying the Recycle Bin
* dba_recyclebin or user_recyclebin
—Shows all objects (tables and indexes)

*SHOW RECYCLEBIN in SQL*Plus
—Only details of dropped tables are shown

* Data can be queried in 'binned' tables as follows
SELECT * FROM “BIN$xyWe0+q+SniItJ0pn/u54A==$0” [AS OF ...];

Purging Objects in the Recycle Bin
Object(s) can be purged from the recycle bin with:
-- Purges specified version of a dropped table
PURGE TABLE “BIN$xyWe0+q+SniItJ0pn/u54A==$0”;

-- purges earliest dropped version of emp
PURGE TABLE emp;

PURGE RECYCLEBIN;
PURGE TABLESPACE user1;
PURGE TABLESPACE user1 USER fred;
PURGE INDEX "BIN$FTX34MN88J7==$0”
;

--Users with SYSDBA privilege, can purge the entire recycle bin
PURGE DBA_RECYCLEBIN;

Restoring Objects in the Recycle Bin
During this process, the table reverts to its original name. All its dependent objects are recovered (except referential constraints)

Flashback drop will restore the most recent version of a table from the recycle bin
FLASHBACK TABLE emp TO BEFORE DROP;

A specific version can also be restored, If same table is dropped more than once, each dropped version is given a different ‘bin’ name
FLASHBACK TABLE “BIN$xyWe0+q+SniItJ0pn/u54A==$ TO BEFORE DROP;

You can use RENAME to avoid name clashes with new objects of the same name
FLASHBACK TABLE emp TO BEFORE DROP RENAME TO employees;


Flashback Database
Fast (point in time) recovery from ‘recent’ logical errors
—Effectively a rollback of the database
—An alternative to normal recovery mechanisms

Setting up Flashback Database
1.Configure the recovery area by setting the following dynamic parameters
DB_RECOVERY_FILE_DEST
—Oracle writes flashbacks logs to the specified recovery area (destination)
—Default value is ORACLE_BASE\flash_recovery_area

DB_RECOVERY_FILE_DEST_SIZE
—Default size is 2GB

DB_FLASHBACK_RETENTION_TARGET (default 1440 minutes)
—Oracle will try to keep enough flashback information to rollback through 1440 minutes

2. Find the current SCN of the database in case you need to perform a subsequent ‘flashback’ to the current state

3. ALTER DATABASE FLASHBACK ON;

4. You can use these commands to Flashback the Database:
Database must be in ARCHIVELOG mode
FLASHBACK DATABASE TO TIMESTAMP ...
FLASHBACK DATABASE TO BEFORE TIMESTAMP ...
FLASHBACK DATABASE TO BEFORE SCN ...
FLASHBACK DATABASE TO SCN ...


The Recovery Area is designed to hold flashback logs, archive logs, backupsets and image (datafile) copies. You should size it to keep enough flashback to comply with the retention period. An alert is raised when Recovery Area is 85% full and the files are deleted when 100% full. The chosen files to be deleted can be obsolete backups or flashback logs
Recovery Manager (RMAN) automatically deletes flashback logs in preference to other files needed for backup purposes. It's recommended not to use FRA in a non-RMAN environment.


Useful Views
Use v$flashback_database_log to show
—Available recovery window
—Actual size of flashback data
—An estimate of amount of flashback needed to support retention target

Use v$recovery_file_dest to show the total space usage in recovery area

SELECT name, space_limit, space_used, reclaimable_space reclaim, number_of_files files
FROM v$recovery_file_dest;
NAME                          SPACE_LIMIT SPACE_USED RECLAIM FILES
----------------------------- ----------- ---------- ------- -----
D:\oracle\flash_recovery_area  2147483648  364353536       0    86


Use v$flashback_database_stat to show write activity at hourly intervals:
SELECT TO_CHAR(begin_time,'ddth hh24:mi') start_time, TO_CHAR(end_time,'ddth hh24:mi') end_time, db_data,
       redo_data, flashback_data fl_data, estimated_flashback_size est_fl_size
FROM v$flashback_database_stat;

START_TIME END_TIME   DB_DATA    REDO_DATA FL_DATA EST_FL_SIZE
---------- ---------- ---------- ---------- ---------- -----------
15th 15:59 15th 16:48   20234240    9678336   12361728           0
15th 14:59 15th 15:59   19652608    7720960   10272768   398499840
15th 13:59 15th 14:59   21643264    8264704   12541952   447406080
15th 12:59 15th 13:59   20897792    7571968   12435456   516833280
15th 11:52 15th 12:59   46702592   32384000   33333248   712679424


estimated_flashback_size is the value found in v$flashback_database_log at the end of the time interval

Flashback Database Features
* Cannot recover from media failure such as loss of a datafile
* Cannot flashback past a shrink datafile
—But can handle datafile automatic expansion
* Required flashback logs must be available
* Can also be used in a Data Guard environment
—Used with snapshot standby databases
* Flashback data requires a lot of space
—On Windows, logs are ~ 8MB on a ‘quiet’ system with names like O1_MF_0B87CPH6_.FLB
—Any change within a block means the whole block is logged

Flashback must be performed in a mount state and requires an :
ALTER DATABASE OPEN RESETLOGS;

But what happens if you are not sure that your flashback is to the correct point in time?
1- Open the database in READ ONLY mode to observe the data
ALTER DATABASE OPEN READ ONLY;
2- Shutdown
3- Mount the database
4- Flashback to a different point


Restore Points
These are named markers for FLASHBACK DATABASE, and avoids the need for SCNs or timestamps.
You can use it to "mark" a position before potentially risky operations that could compromise the database.
Can be normal or guaranteed, Normal restores require FLASHBACK mode. Guaranteed restores can be used when database not in FLASHBACK mode
CREATE RESTORE POINT before_upgrade
[GUARANTEE FLASHBACK DATABASE];


Using Restore Points
FLASHBACK DATABASE TO RESTORE POINT before_major_change;
FLASHBACK TABLE TO RESTORE POINT before_major_change;
RECOVER DATABASE TO RESTORE POINT before_major_change;


You can also remove restore points with:
DROP RESTORE POINT before_major_change;

—Normal restore points age out of control file
—Guaranteed restore points are never removed
—Database cannot be taken out of archivelog mode if a guaranteed restore point is present


Flashback Data Archive

This feature transparently tracks historical changes to data in selected tables. The Flashback Data Archiver (FBDA) process is in charge of writing the writes the changes.
The FBDA process takes read consistent data from buffer cache and/or undo tablespace into the archive tablespace.
The archives managed by account with FLASHBACK ARCHIVE ADMINISTER privilege. Archive users need FLASHBACK ARCHIVE object privilege on the flashback archive object to allow history tracking for specific tables.
The data is retained according to a time specification and is automatically purged based on retention policy.
It minimize performance impact of capturing historical data. The data is compressed in tablespaces to minimize storage requirements.
It's capable of capturing DDL changes

Setting up Flashback Archives
• Create a tablespace dedicated for the FB Archive:
CREATE TABLESPACE TBS_FBA1 DATAFILE 'c:\oracle\oradata\orcl\TBS_FBA1.dbf' SIZE 10G SEGMENT SPACE MANAGEMENT AUTO;

• Create a FB Archive
create flashback archive FB1 tablespace TBS_FBA1 retention 1 year;
or
ALTER FLASHBACK ARCHIVE FB1 MODIFY RETENTION 6 MONTH;

• (Optional) Allow a user to track changes to his tables in the fba1 archive
GRANT FLASHBACK ARCHIVE ON fb1 TO fred;

• Enable history tracking for a table in the FB1 archive
CONN fred/fred
CREATE TABLE emparch
(empno NUMBER(4) ,
 ename VARCHAR2(12) ,
 sal NUMBER(7,2) ,
 comm NUMBER(7,2)
)
FLASHBACK ARCHIVE fb1;

or
ALTER TABLE emp FLASHBACK ARCHIVE fb1;

• View the historical data
SELECT empno, ename, sal
FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2009-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
or
SELECT empno, ename, sal
FROM emp AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL '20' DAY);


• Query FDA Dictionary Information
SELECT * FROM dba_flashback_archive;
FLASHBACK_   FLASHBACK_ RETENTION_ CREATE_TIME                  LAST_PURGE_TIME              STATUS
ARCHIVE_NAME ARCHIVE#   IN_DAYS
------------ ---------- ---------- ---------------------------- ---------------------------- -------
FB1                   1         30 13-NOV-08 14.24.28.000000000 13-NOV-08 14.24.28.000000000


SELECT * FROM dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ TABLESPACE_NAME QUOTA_IN_MB
                       ARCHIVE#
---------------------- ---------- --------------- -----------
FB1                             1 TBS_FBA1               5000


SELECT * FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
---------- ---------- ---------------------- ------------------
EMPARCH    SYS        FB1                    SYS_FBA_HIST_69882


• After some time, you can query the ARCH Table:
SELECT * FROM sys_fba_hist_69882;
RID                STARTSCN ENDSCN XID               O EMPNO ENAME  SAL  COMM
------------------ -------- ------- ---------------- - ----- ------ ---- ----
AAAR0nAABAAAVxiAAA  5432321 5432342 02001D00C3110000 I  7369 SMITH   800
AAAR0nAABAAAVxiAAB  5432321 5432342 02001D00C3110000 I  7499 ALLEN  1600  300
AAAR0nAABAAAVxiAAC  5432321 5432342 02001D00C3110000 I  7521 WARD   1250  500
AAAR0nAABAAAVxiAAD  5432321 5432342 02001D00C3110000 I  7566 JONES  2975
AAAR0nAABAAAVxiAAE  5432321 5432342 02001D00C3110000 I  7654 MARTIN 1250 1400
AAAR0nAABAAAVxiAAF  5432321 5432342 02001D00C3110000 I  7698 BLAKE  2850
AAAR0nAABAAAVxiAAG  5432321 5432342 02001D00C3110000 I  7782 CLARK  2450
AAAR0nAABAAAVxiAAH  5432321 5432342 02001D00C3110000 I  7788 SCOTT  3000
AAAR0nAABAAAVxiAAI  5432321 5432342 02001D00C3110000 I  7839 KING   5000
AAAR0nAABAAAVxiAAJ  5432321 5432342 02001D00C3110000 I  7844 TURNER 1500    0
AAAR0nAABAAAVxiAAK  5432321 5432342 02001D00C3110000 I  7876 ADAMS  1100
AAAR0nAABAAAVxiAAL  5432321 5432342 02001D00C3110000 I  7900 JAMES   950
AAAR0nAABAAAVxiAAM  5432321 5432342 02001D00C3110000 I  7902 FORD   3000
AAAR0nAABAAAVxiAAN  5432321 5432342 02001D00C3110000 I  7934 MILLER 1300


• Purges automatically. Manually:
alter flashback archive FB1 purge before scn 1234567;
or
ALTER FLASHBACK ARCHIVE FB1 PURGE BEFORE TIMESTAMP(SYSTIMESTAMP – INTERVAL '1' DAY);

• Drops the history table
ALTER TABLE scott.emp NO FLASHBACK ARCHIVE;


Tips for Using Flashback Data Archive
- COMMIT or ROLLBACK before querying past data
- Tables that are being archived cannot be dropped, Archiving must first be disabled.
- Tablespaces that contain archive-enabled tables cannot be dropped

Flashback Data Archiving versus Oracle Auditing
- Auditing needs the audit_trail parameter to be set to DB or DB_EXTENDED
- Trails are written to aud$ in the SYSTEM tablespace. Flashback Data can be archived in any tablespace and can be defined on cheaper storage.
- Auditing uses autonomous transactions, which has some performance overhead
- FDA written in background by FBDA process – gives less impact on performance


Modify the Flashback Data Archive
Use ALTER FLASHBACK ARCHIVE command to change the retention time, purge data or add or remove the tablespace. For this you need to have FLASHBACK ARCHIVE ADMINISTER privilege granted.  
-- To change the retention time, use:
alter flashback archive fl_archive modify retention 6 month;


-- To change tablespace quota of the tablespace that is used by a flashback data archive, use:
alter flashback archive fl_archive add tablespace tbs_arch quota 50m;

 
-- To add another tablespace for flashback data archive, use:
create tablespace tbs_arch2 datafile 'c:\flashback_archive2.dbf' size 10m;
alter flashback archive fl_archive add tablespace tbs_arch2 quota 10m;

 

-- To remove the tablespace from use by flashback data archive, use:
alter flashback archive fl_archive remove tablespace tbs_arch2;


-- To purge the data that’s in Flashback Data Archive, use PURGE BEFORE SCN (TIMESTAMP) or PURGE ALL as follows:
ALTER FLASHBACK ARCHIVE fl_archive PURGE ALL;
ALTER FLASHBACK ARCHIVE fl_archive purge before timestamp to_timestamp('13022010 12:49:30','ddmmyyyy hh24:mi:ss');
ALTER FLASHBACK ARCHIVE fl_archive purge before scn 988827;



Dropping Flashback Data Archive
To drop flashback data archive use:
drop flashback archive fl_archive;


Using default Flashback Data Archive for the system
As default, Oracle doesn’t use any flashback data archive. To set default flashback data archive, use the following command by connecting as SYS user:
conn / as sysdba
alter flashback archive fl_arc set default;

By setting default flashback data archive, you will not need to specify it manually on each table you want to keep changes

To disable flashback archive for a table, use:
alter table tbl_fl_archive no flashback archive;


Query Flashback Data Archive
There are mainly three views that are used to query the information regarding Flashback Data Archive.
The first view is DBA_FLASHBACK_ARCHIVE which gives information about Flashback Data Archive files
select * from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                     LAST_PURGE_TIME     
---------------------- -----------------  -----------------
------------------------------  ------------------------------
FL_ARCH                      2                365           13-FEB-10 08.05.14.000000000 PM 13-FEB-10 08.05.14.000000000 PM  


The second view is DBA_FLASHBACK_ARCHIVE_TS which gives information about tablespace that contains flashback data archives:
select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME      FLASHBACK_ARCHIVE#  TABLESPACE_NAME  QUOTA_IN_MB
----------------------     ------------------   ---------------  ----------
FL_ARCH                         2               TBS_ARCH         FL_ARC


The third view is FLASHBACK_ARCHIVE_TABLES which displays the information of the tables that uses Flashback Data Archive:
select * from dba_flashback_archive_tables;
TABLE_NAME     OWNER_NAME  FLASHBACK_ARCHIVE_NAME   ARCHIVE_TABLE_NAME
-------------- ----------  ----------------------   ------------------
TBL_FL_ARCHIVE US1         FL_ARC                   SYS_FBA_HIST_69845



As it was told above, Flashback Data Archie technology stores data in Compressed and Partitioned format. It should be checked easily. Get the name of the main table that’s used to store the data and query USER_TAB_PARTITIONS view as follows:
select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SYS_FBA_HIST_69845
SYS_FBA_TCRV_69845             TBS_ARCH
SYS_FBA_DDL_COLMAP_69845       TBS_ARCH
TBL_FL_ARCHIVE                 USERS
 
select table_name, partition_name, compression, compress_for from user_tab_partitions where table_name='SYS_FBA_HIST_69845';
TABLE_NAME                     PARTITION_NAME                 COMPRESS
----------------           --------------                 ---------
SYS_FBA_HIST_69845             HIGH_PART                      ENABLED