Crazy Idea of Mixing Flashback Data Archive and Editioning


Now that Flashback Data Archive is now included in the base 11.2.0.4 license, I had the crazy idea to mix it with Database Editioning.

License Update

The License Manual says:

  • Flashback Data Archive (formerly known as Total Recall)
  • For releases earlier than Oracle Database 11g Release 2 (11.2.0.4): You must license the Oracle Advanced Compression option to use Flashback Data Archive.

    Beginning with Oracle Database 11g Release 2 (11.2.0.4): Flashback Data Archive—without history table optimization—is available in all editions.

Overview

In this example, I am using a 12.1.0.2 Personal Edition database. Although I have a Personal Edition licence which includes the use of Advanced Compression Option, I decided not to avail myself of it for this exercise.

The sequence is:

  1. Create a hierarchy of editions representing the months so far in the current financial year.
  2. Set up a default flashback data archive in a separate tablespace, FLASHBACK_ARCHIVE.
  3. Create test table and populate it with data representing the months so far in the current financial year.
  4. Enable editioning on the test table.
  5. Create different editions of the same view on the test table to represent snapshots at different months
  6. Validate that this works

Create the Required Editions

Since editions have a single parent and an optional single child, the editions have to be created in a simple hierarchy as follows:

CREATE EDITION "2015-July";
CREATE EDITION "2015-August"    AS CHILD OF "2015-July";
CREATE EDITION "2015-September" AS CHILD OF "2015-August";
CREATE EDITION "2015-October"   AS CHILD OF "2015-September";
CREATE EDITION "2015-November"  AS CHILD OF "2015-October"; 
CREATE EDITION "2015-December"  AS CHILD OF "2015-November";

The parent of the top edition is ORA$BASE.

Set Up Flashback Data Archive

A separate tablespace, FLASHBACK_ARCHIVE, is created to hold the default flashback data archive, FLASHBACK_ARCHIVE.

CREATE TABLESPACE flashback_archive;
ALTER TABLESPACE flashback_archive AUTOEXTEND ON MAXSIZE 5G;
CREATE FLASHBACK ARCHIVE DEFAULT default_archive
  TABLESPACE flashback_archive
  QUOTA 2G
  NO OPTIMIZE DATA
  RETENTION 7 DAY;

I understand from the licencing manual that the OPTIMIZE DATA clause requires a licence for Advanced Compression Option.

Note: the sizes given here are for demonstration purposes only and are totally impracticable for a production system.

Create Test Table and Populate It

Demonstrating my complete lack of imagination, I called the test table, TEST_USER.TEST_TABLE. This table has two (2) columns:

  1. KEY—primary key. This value is always 1.
  2. DATA—primary key. This value reflects the month.
CREATE TABLE test_user.test_table(
    key NUMBER PRIMARY KEY,
    data VARCHAR2(200) NOT NULL)
  FLASHBACK ARCHIVE;

I use the following script to populate the table and record the SCN after each DML:

ALTER SESSION SET current_schema=test_user;
INSERT INTO test_table(key, data) VALUES(1, 'July 2015');
COMMIT;
SELECT current_scn FROM v$database;
UPDATE test_table SET data = 'August 2015' WHERE key = 1;
COMMIT;
SELECT current_scn FROM v$database;
UPDATE test_table SET data = 'September 2015' WHERE key = 1;
COMMIT;
SELECT current_scn FROM v$database;
UPDATE test_table SET data = 'October 2015' WHERE key = 1;
COMMIT;
SELECT current_scn FROM v$database;
UPDATE test_table SET data = 'November 2015' WHERE key = 1;
COMMIT;
SELECT current_scn FROM v$database;
UPDATE test_table SET data = 'December 2015' WHERE key = 1;
COMMIT;
SELECT current_scn FROM v$database;

Enable Editions

Only views are enabled for editioning in this demonstration:

ALTER USER test_user ENABLE EDITIONS FOR VIEW;

Create the Editioned Views

From the SCNs collected above, I use flashback query to create each edition of the view, TEST_VIEW:

ALTER SESSION SET edition = "2015-July";
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_table AS OF SCN 1408993;
ALTER SESSION SET edition = "2015-August";
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_table AS OF SCN 1408996;
ALTER SESSION SET edition = "2015-September";
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_table AS OF SCN 1408999;
ALTER SESSION SET edition = "2015-October";
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_table AS OF SCN 1409002;
ALTER SESSION SET edition = "2015-November";
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_table AS OF SCN 1409005;
ALTER SESSION SET edition = "2015-December";
CREATE OR REPLACE VIEW test_view AS SELECT * FROM test_table AS OF SCN 1409008;

Validate

And to my surprise, this all works!

SQL> ALTER SESSION SET edition = "2015-November";

Session altered.

SQL> SELECT * FROM test_view;

       KEY DATA
---------- --------------------
	 1 November 2015

SQL> ALTER SESSION SET edition = "2015-August";

Session altered.

SQL> SELECT * FROM test_view;

       KEY DATA
---------- --------------------
	 1 August 2015

Reference

For further information, see Using Edition-Based Redefinition and Using Flashback Data Archive.

Advertisements

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