Constraints With Oracle Total Recall (Remember A Day)

11g has many new and potentially useful features which I’m slowly investigating and evaluating.

One feature that has the potential to be particularly useful where I work is “Oracle Total Recall“. It provides the facility to automatically capture all changes to any tables you choose, in a “secure, tamper proof” manner. This enables all changes to be easily tracked and viewed for historical, auditing, security control, etc. purposes.

Thought I might “introduce” Total Recall to those of you that may not have seen it in action and to offer an initial word of warning of a little trap one can easily fall into …

One nice aspect with it all is that it’s very easy to setup and configure.

To begin with, you need to create a storage area called a “flashback data archive” where all the historical changes can be stored, e.g.:

SQL> CREATE FLASHBACK ARCHIVE bowie_archive
        TABLESPACE total_recall_ts QUOTA 500m RETENTION 1 YEAR;

You simply specify the tablespace you wish to use, how much space within the tablespace you wish to allocate for archiving purposes and a retention period to specify how long to keep the archived data.

To create a table with the “Total Recall” change tracking capability, simply specify which flashback archive area to use:

SQL> CREATE TABLE bowie_recall (id number, text varchar2(20), hist_date date) FLASHBACK ARCHIVE bowie_archive;

Table created.

All changes to the table are now automatically tracked and archived and can easily be accessed as necessary. To illustrate, I’m just going to simply insert a row into the table.

SQL> insert into bowie_recall values (1, ‘Bowie’, sysdate);

1 row created.

SQL> commit;

Commit complete.

Sometime later, I perform an update on the row:

SQL> update bowie_recall
  2  set text = ‘Ziggy’, hist_date = sysdate
  3  where id = 1;

1 row updated.

SQL> commit;

Commit complete.

OK, so the current row basically looks like thus:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date
         from bowie_recall;

  ID TEXT     HIST_DATE
---- -------- -------------------
   1 Ziggy    06-10-2008 10:09:55

 

However, if I want to see what the row looked like at a previous point in time, I can use the AS OF TIMESTAMP clause such as this:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date
         from bowie_recall
         as of timestamp to_timestamp(‘2008-10-06 10:07:00’,
                     ‘yyyy-mm-dd hh24:mi:ss’)
         where id = 1;

  ID TEXT     HIST_DATE
---- -------- -------------------
   1 Bowie    06-10-2008 10:06:15

 

This is what the row looked like when it was initially inserted.

If I want to look at all the different versions of the row between any specific times of interest, I can write something such as this using the VERSIONS BETWEEN clause:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date
         from bowie_recall
         versions between timestamp
         to_timestamp(‘2008-10-06 10:07:00’, ‘yyyy-mm-dd hh24:mi:ss’)
         and sysdate
         where id = 1;

  ID TEXT     HIST_DATE
---- -------- -------------------
   1 Ziggy    06-10-2008 10:09:55
   1 Bowie    06-10-2008 10:06:15

 

It lists all versions of the rows between the two points of time …

Like I said, very easy to setup and very easy to subsequently query.

One of the disadvantages however of enabling the tracking of changes to a table is that it prevents a number of subsequent operations on the table. For example, you can’t simply drop a column:

SQL> alter table bowie_recall drop column status;
alter table bowie_recall drop column status
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

You also for example can’t modify a column to say add a NOT NULL constraint:

SQL> alter table bowie_recall modify text not null;
alter table bowie_recall modify text not null
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Now the trap …

Oracle does however allow you to add any other type of constraint, such as say a Primary Key, without complaint:

SQL> alter table bowie_recall add primary key (id);

Table altered.

All well and good, except for when you want to now retrieve some of the historical changes from the table:

SQL> select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date from bowie_recall versions between timestamp to_timestamp(‘2008-10-06 10:07:00’, ‘yyyy-mm-dd hh24:mi:ss’) and sysdate where id = 1;
select id, text, to_char(hist_date, ‘dd-mm-yyyy hh24:mi:ss’) hist_date from  bowie_recall versions between timestamp to_timestamp(‘2008-10-06 10:07:00’, ‘yyyy-mm-dd hh24:mi:ss’) and sysdate where id = 1
                                                                             *
ERROR at line 1:
ORA-01466: unable to read data – table definition has changed

Remember, one of the reasons for implementing Total Recall on a table is to implement a “tamper proof” data tracking solution and yet the addition of any constraint on a table (Primary Key, Unique Key, Foreign Key or Check Constraint) will cause you to be unable to automatically read the historical data, without directly accessing the underlining SYS_FBA_ archive tables.

Be careful with constraint management and the historical tracking of tables …

Total Recall is of course all relatively new and these sorts of early glitches are to be expected. More on Total Recall later, including possible performance implications when both modifying and querying historical data.