Oracle Flashback Time Travel aka Flashback Data Archive ensures you can track changes to the data and the structure of a table.

Flashback Time Travel #JoelKallmanDay

Flashback Time Travel previously called Total Recall lets you track all changes to the data in a table and even structural changes like added or removed columns. This means you can use it in various scenarios from detecting illegal state-transitions caused by bugs in application to compliance where full control of sensitive data is required.

You achieve this by adding tracking to the table(s) either during creation or later with alter table and the changes to the table will be stored in what is called a flashback data archive or just flashback archive.

Setting it up

First you should set up a separate tablespace:

create bigfile tablespace flashback_archive_ts 
    datafile size 1g autoextend on next 1g;

With the SYSTEM or a user with the FLASHBACK ARCHIVE ADMINISTER system privilege you create a flashback archive with:

create flashback archive default flashback_archive
tablespace flashback_archive_ts 
quota 10G retention 6 month;

This will create the default flashback archive in the database (you can only have one default, but many flashback archives). In this example it limits the quota to 10GB and keep the changes for 6 months (you can specify and integer and day, month or year). You can even add another tablespace with another quota to the same flashback archive later (think cheap storage with a bigger quota).

In order for a user to add tracking to a table using this flashback archive it needs the object privilege FLASHBACK ARCHIVE on this:

grant flashback archive on flashback_archive to scott;

This is how you add flashback archive to an existing table:

alter table cards flashback archive;

This assumes that there is a default flashback archive in the database. Alternatively you can specify it:

alter table cards flashback archive flashback_archive;

Likewise, you can enable this when you create the table:

create table xyz (
    id number,
    zoo varchar2(4000)) flashback archive flashback_archive;

Before I show how to do something useful, I should mention how to remove this. The syntax is straight forward:

alter table cards no flashback archive;

However, a user needs the system privilege FLASHBACK ARCHIVE ADMINISTER to do this! This kind of makes sense. A developer may conclude that a table should have flashback data archive enabled, but should not be able to remove it (temporarily). So we have some separation of duties here. Also, you cannot drop a table (and perform a few other DDL operations) with flashback archive enabled, so think twice before you add it.

Going back in time

You can query the table as it were in the past by adding the as of timestamp clause, like this:

select count(*)
from cards as of timestamp sysdate - 2/24;

Note, you can actually do this without a flashback archive as long as you have enough undo data available in the database (check undo_retention parameter, maybe you should incease it to cover a whole business day if you have enough space), but with flashback data archive you can go back as much as specified by the retention in the flashback archive, or to the point in time it was enabled for the table. Also, a flashback query on a table that has been changed with an alter table statement at a later point will not work without a flashback archive.

Looking at different versions of a row

This is really useful when you need to look into unexpected data changes. Suppose something strange happened to a credit card, this fetches all versions of a row with one specific card_id the last 24 hours:

select card_number,customer_id,VERSIONS_OPERATION,VERSIONS_STARTTIME,VERSIONS_XID
from card_details versions between timestamp sysdate - 1 and sysdate
where card_id=1424242
order by versions_starttime;

   CARD_NUMBER    CUSTOMER_ID VERSIONS_OPERATION    VERSIONS_STARTTIME                 VERSIONS_XID        
______________ ______________ _____________________ __________________________________ ___________________ 
    1136648200         865426 U                     06-APR-24 07.03.07.000000000 AM    080021008B030000    
    1136648200         865426 U                     06-APR-24 07.19.09.000000000 AM    05000D0061030000    
    1136648200         865427 U                     06-APR-24 07.56.09.000000000 AM    020004001D030000    
    1136648200         865428 U                     06-APR-24 08.08.06.000000000 AM    090015005F030000    
    1136648200             42 U                     06-APR-24 05.01.11.000000000 PM    090009006C030000    
    1136648200         865426

A version query like this gives you new pseudocolumns:

VERSIONS_OPERATION will show I, U, or D for Insert, Update, or Delete
VERSIONS_STARTTIME will show when this version was created/started.
VERSIONS_XID is the transaction id

The transaction id (VERSIONS_XID) is what you need in order to investigate further in the audit trail.

If you have traditional audit enabled on a table this will show who did it:

select timestamp,os_username,userhost,username
from user_audit_object
where transactionid = hextoraw('090009006C030000');

(You don’t have to use the hextoraw function above, but with it an index will be used and the query should run faster.)

In the new unified audit the query is:

select os_username,userhost,EVENT_TIMESTAMP,sql_text
from unified_audit_trail
where transaction_id = hextoraw('090009006C030000');

Detecting illegal state changes

Status columns are common to indicate the state of a process or some lifecycle flow. I have been asked to investigate why some rows have been seemingly reversed in a process. We could have solved that with just adding a trigger that aborts illegal state changes, but using flashback data archive we reported on the unexpected change and investigated further.

Suppose you have a credit card that was cancelled by the owner, but then a rogue operator enabled it later:

select *
from (
  select card_id,is_valid, 
    lag(is_valid) over (partition by card_id order by versions_starttime) prev_is_valid,
    versions_starttime, versions_operation
  from card_details versions between timestamp sysdate - 1 and sysdate
)
where is_valid ='Y' and prev_is_valid='N';

This rather simple analytical query in the inner query adds a column showing previous value for a column, the outer query filters on transitions that are not OK. With the pseudocolumn you can investigate further on what happened.

Documentation

Flashback technology is documented in chapter 22 in the Database Development Guide (19c) . It has more details regarding the syntax, options and limitations. The SQL syntax is documented in the SQL Language Reference .

Conclusion

I just touched a few areas where a flashback data archive is useful. It goes without saying that you need to configure this before you need it. It is not something you would enable for all tables, but for a few critical tables or temporarily in order to investigate a bug.

Flashback Archive is just one of a many flashback technologies. I gave a presentation on all flashback technologies at POUG2024 last week. You should absolutely check out that conference for next year. The POUG conference is probably the best Oracle user group conference that exists. Try to get in touch with me if you want a copy of the presentation, the PDF-version has more details and code examples.