Flashback Data Archives

Flashback data archives store changes made to a table over a period of time. Flashback data archives provide you with a full audit trail. You can view rows audit log at a specific timestamp or between two timestamps.

You create a flashback archive using the CREATE FLASHBACK ARCHIVE statement.



CREATE FLASHBACK ARCHIVE test_archive
TABLESPACE example
QUOTA 1 M
RETENTION 1 DAY;

To get table space name:


SELECT tablespace_name FROM dba_tablespaces;

The test_archive has a quota of 1 megabyte.


ALTER TABLE store.employee FLASHBACK ARCHIVE test_archive;

Any subsequent changes made to the store.employee table are now recorded in the archive. You can view the rows as they were 5 minutes ago using the following query:


SELECT id, name, salary
FROM store.employee
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

You can also view the rows as they were at a specific timestamp using the following query:


SELECT id, name, salary
FROM store.employee
AS OF TIMESTAMP
TO_TIMESTAMP('2011-06-12 13:05:00', 'YYYY-MM-DD HH24:MI:SS');

You can view the rows as they were between two timestamps using the following query:


SELECT id, name, salary
FROM store.employee 
VERSIONS 
BETWEEN TIMESTAMP
TO_TIMESTAMP('2011-06-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2011-06-12 12:59:59', 'YYYY-MM-DD HH24:MI:SS');

You can view the rows as they were between one timestamp and the present time :


SELECT id, name, salary
FROM store.employee VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2012-06-12 13:45:52', 'YYYY-MM-DD HH24:MI:SS')
AND MAXVALUE;

You can stop archiving of data for a table using ALTER TABLE; for example:


ALTER TABLE store.products NO FLASHBACK ARCHIVE;

When you create a table, you can specify a flashback archive for that table; for example:


CREATE TABLE store.test_table (
id INTEGER,
name VARCHAR2(10)
)FLASHBACK ARCHIVE test_archive;

Home »
Oracle »
Table »

Flashback Data Archives: