top of page

Flashback Time Travel

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Flashback Time Travel is and gives a small demo example.


What it is:

Flashback Time Travel is a feature of Oracle Flashback Technology that lets you store and track all changes to the data in your table(s) for a decided amount of time including across DDL changes on tracked tables. This could be useful for a handful of reasons

  • Compliance - you want to keep a historical record of your data or save it for compliance reports and decide to track all changes to the table(s) for a certain amount of time

  • Security - you have sensitive info in your tables and want to be sure you track the history of all data changes

  • Accessing historical data - you want to setup a way to recover back to a prior point in time for selective tables without having to perform full database backup or recovery operations


Flashback Time Travel uses Flashback Archive to store the changes on the tables you're tracking. The Flashback Archive is a logical entity that is associated with the tablespace(s) you create where the tracked changes are saved. The Flashback Archive can have one or more tablepaces associated, and you can have multiple Flashback Archives. Each Flashback Archive has a set amount of time that it will track the changes. You define how long the retention time will be.


Flashback Time Travel feature is essentially a combination of the Flashback Archive and Flashback Query 'SELECT AS OF' features


In Autonomous Database, Flashback Time Travel is enabled by default, however, that doesn't mean that the tables are enabled. Each ADB comes with a predefined Flashback Archive called flashback_archive, meaning you don't have to worry about managing the tablespace but still need to enable the feature on the table(s) you want to track.



A few important things to mention when using Flashback Time Travel:

  • Wait at least 20 seconds after enabling Flashback Archive before inserting data

  • For best performance, wait up to 5 minutes before running your first Flashback Query

  • Monitor your archive space usage - when it exceeds 90% of the quota, operations may slow down

  • Remember that you can't enable Flashback Archive on temporary tables, external tables, or tables with LONG columns


Flashback Time Travel in Action

Let's walk through an example of setting up and using Flashback Time Travel. We'll create a simple employee table that tracks salary changes, and show how to:

  1. Set up a Flashback Archive

  2. Enable it for our table

  3. Make some changes

  4. Query historical data


Note: If you want a free Autonomous Database you can setup in less than 10 minutes, check out this blog


First, let's create a Flashback Archive. This requires SYSDBA privileges:

CREATE FLASHBACK ARCHIVE emp_archive TABLESPACE users QUOTA 10G RETENTION 1 YEAR;

Now, let's create our employees table with Flashback Archive enabled:

CREATE TABLE employees (
    emp_id    NUMBER PRIMARY KEY,
    name      VARCHAR2(50),
    salary    NUMBER,
    dept      VARCHAR2(20)
) FLASHBACK ARCHIVE emp_archive;

Let's insert some initial data:

INSERT INTO employees VALUES (1, 'John Smith', 50000, 'IT'),
							(2, 'Sarah Jones', 60000, 'Sales');

COMMIT;

Now, let's create some salary changes over time:

-- First salary update
UPDATE employees SET salary = 55000 WHERE emp_id = 1;
COMMIT;

-- Wait a minute...
-- Second salary update
UPDATE employees SET salary = 65000 WHERE emp_id = 2;
COMMIT;

We can now query this table's history in a couple ways:


1. View the data as it was at a specific point in time:

-- See the data as it was 1 minute ago
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);


2. We can also see all versions of a specific record:

-- See all salary changes for John Smith
SELECT emp_id, name, salary, versions_operation, versions_starttime, versions_endtime
FROM employees VERSIONS BETWEEN TIMESTAMP 
    MINVALUE AND MAXVALUE
WHERE emp_id = 1
ORDER BY versions_starttime;


The Row Data Pseudo column versions_operations are as follows:

  • I - insert

  • U - update

  • D - delete


3. We could also compare current data with historical data

-- Compare current salaries with those from the beginning of the year
SELECT 
    e.emp_id,
    e.name,
    e.salary as current_salary,
    h.salary as original_salary
FROM employees e,
     employees AS OF TIMESTAMP 
        TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') h
WHERE e.emp_id = h.emp_id;

You can also protect your historical data using blockchain capabilities:

CREATE TABLE sensitive_employees (
    emp_id    NUMBER PRIMARY KEY,
    name      VARCHAR2(50),
    salary    NUMBER,
    dept      VARCHAR2(20)
) BLOCKCHAIN FLASHBACK ARCHIVE emp_archive;

This blockchain version provides tamper-proof historical records, good for audit and compliance requirements.


That's Flashback Time Travel in action.


There is a lot more info available in the Flashback Technology documentation


-Killian

Comentarios


bottom of page