Flashback Data Archive Requirements:

3 Jan

1. Tablespaces managed with automatic segment space management (ASSM) are required for using with Flashback Data Archive.

2. Automatic Undo Management must be enabled.

Step-by-Step Approach

There are 3 steps to enabling history tracking on tables using Flashback Data Archive.

1. Create a new tablespace with automatic segment space management for storing historical data (Optional).
2. Create a flashback data archive.

SQL> Create Flashback Archive fb_ddo Tablespace ddo Retention 5 Year ;

This creates a new flashback data archive called ‘fb_ddo’ on tablespace ddo with a retention policy of 5 years. Since ‘QUOTA’ is not specified, the default setting of Unlimited is used. At this point, we have created a logical container or placeholder for historical data.

Flashback Archive Administer system privilege is required for creating a new flashback data archive.

The following static data dictionary views are available.
DBA/USER_FLASHBACK_ARCHIVE : Displays information about Flashback Data Archives.
DBA/USER_FLASHBACK_ARCHIVE_TS : Displays tablespaces and the mapping to Flashback Data Archives.

3. Enable history data archiving desired tables.

SQL> Alter table employees FLASHBACK ARCHIVE fb_ddo ;

Table Employees now has been enabled for historical data tracking and its history data will be stored in flashback data archive ‘fb_ddo’. When the first DML operation happens, the internal history table is created in ‘fb_ddo’.

FLASHBACK ARCHIVE: Object privilege is required to enable historical data tracking.

The following static data dictionary views are available.

DBA/USER_FLASHBACK_ARCHIVE_TABLES: Displays information about tables that are enabled for flashback archiving.

Querying History Data :

Flashback Data Archive provides aeamless access to the historical data using the ‘AS OF’ SQL construct. You can query for the state of any row in the tracked table going as far back as your specified retention period.

The following is an example for querying the salary details for employees with id=51111 on September 20, 2007.

SQL> Select employeeName, employeeSalary from employees as of TIMESTAMP TO_TIMESTAMP (‘2007-09-20 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’) where employee_id = 51111 ;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: