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:
Set up a Flashback Archive
Enable it for our table
Make some changes
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