Using Flashback Data Archive? Never heard of Flashback Data Archive? Maybe a brief overview of what Flashback Data Archive is would be useful before we discuss how to compress the history tables managed by Flashback Data Archive.
Oracle’s Flashback Data Archive (FDA) feature provides a mechanism for tracking changes to production databases that is secure, efficient, easy to use and application transparent. FDA allows organizations to configure historical data capture in a matter of minutes, providing a centralized and seamlessly queryable historical data store.
Flashback Data Archive is configured with a retention time and data archived by Flashback Data Archive is retained, for the retention time specified, in associated history tables that are managed by FDA. Organizations can use Flashback Data Archive in the same manner as other Flashback features to view or restore the data as of a time (or time range) in the past.
So where does compression come into play? The source production tables can be compressed using either Advanced Row Compression or Hybrid Columnar Compression. However, the topic of this blog is the compression of the history tables managed by Flashback Data Archive.
As the SQL syntax indicates below, to compress history tables, specify OPTIMIZE DATA to enable optimization (compression) for Flashback Data Archive history tables. This instructs the database to optimize the storage of data in history tables using Advanced Row Compression (requires Oracle Advanced Compression). Specify NO OPTIMIZE DATA to instruct the database not to optimize (compress) the storage of data in the history tables.
CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive
[ [ NO] OPTIMIZE DATA ]
The default is NO OPTIMIZE DATA.
For more information (and usage examples) about Flashback Data Archive and OPTIMIZE DATA, please see:
Oracle® Database Development Guide. (See here)
Oracle Flashback Data Archive white paper. (See here)
I have no idea what we will discuss in next month’s blog, but I will figure out something before then. Have a great summer!
The database storage optimization adventure continues!