Oracle Cloud Infrastructure Documentation

Track Table Changes with Flashback Time Travel

Use Flashback Time Travel to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

Parent topic: High Availability

About Flashback Time Travel

Flashback Time Travel lets you track and store transactional changes to a table over its lifetime. Flashback Time Travel is useful for compliance with record stage policies and audit reports. You can also use Flashback Time Travel in various scenarios such as enforcing digital shredding, accessing historical data, and selective data recovery.

Flashback Time Travel was called Flashback Data Archive in previous Oracle Database versions. The Flashback Time Travel APIs and some Flashback Time Travel terms such as the FLASHBACK ARCHIVE privilege retain the Flashback Data Archive naming.

Each Autonomous Database instance has one Flashback Archive named flashback_archive that supports Flashback Time Travel operations. The default retention time for flashback_archive is 60 days. See Modify the Retention Time for Flashback Time Travel for information on changing the default retention time.

There are restrictions to enable Flashback Time Travel for a table, including the following:

  • You must have the FLASHBACK ARCHIVE object privilege for flashback_archive. By default the ADMIN user has this privilege.
  • The table must not be any of the following:
    • A nested table
    • A temporary table
    • A remote table
    • An external table
  • The table cannot contain LONG columns.
  • The table cannot contain nested columns.

See Notes for Flashback Time Travel for a list of additional Flashback Time Travel restrictions.

See Using Oracle Flashback Technology for more information on Flashback Technology.

Enable Flashback Time Travel for a Table

Describes steps to enable Flashback Time Travel for a table in Autonomous Database.

By default, Flashback Time Travel is disabled for new and existing tables.

The following is the prerequisite to enable Flashback Time Travel:

  • You must be logged in as the ADMIN user or have the FLASHBACK ARCHIVE object privilege.

    The ADMIN user or the user having the FLASHBACK ARCHIVE privilege WITH GRANT OPTION can grant the FLASHBACK ARCHIVE object privilege to another user.

    For example:

    GRANT FLASHBACK ARCHIVE ON FLASHBACK_ARCHIVE TO <username>
    GRANT FLASHBACK ARCHIVE ON FLASHBACK_ARCHIVE TO <username> WITH GRANT OPTION

    See Manage User Roles and Privileges on Autonomous Database for more information.

You can enable Flashback Time Travel for an existing table or a new table that you are creating.

  1. To enable Flashback Time Travel when you create a table:
    CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;
  2. To enable the Flashback Time Travel for an existing table:
    ALTER TABLE departments FLASHBACK ARCHIVE;

After you enable Flashback Time Travel for a table you can disable Flashback Time Travel. See Disable Flashback Time Travel for a Table for more information.

Disable Flashback Time Travel for a Table

Describes procedure to disable Flashback Time Travel for a table in Autonomous Database.

After Flashback Time Travel is enabled for a table you can disable it as the ADMIN user or if you have the FLASHBACK ARCHIVE object privilege.

For example, to disable Flashback Time Travel for the employee table:
ALTER TABLE employee NO FLASHBACK ARCHIVE;

Modify the Retention Time for Flashback Time Travel

Describes procedure to modify the retention time for Flashback Time Travel in Autonomous Database.

You can modify the retention time for Flashback Time Travel if you are logged in as ADMIN user or if you have EXECUTE privilege on DBMS_CLOUD_ADMIN.

For example, to set the retention time to 365 days:
BEGIN
  DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION
   (retention_days => 365);
END;
/

See SET_FLASHBACK_ARCHIVE_RETENTION Procedure for detailed information about the procedure.

Purge Historical Data for Flashback Time Travel

Describes procedure to purge historical data for Flashback Time Travel in Autonomous Database.

You can purge historical data for Flashback Time Travel if you are logged in as the ADMIN user or if you have EXECUTE privilege on DBMS_CLOUD_ADMIN.

For example, to purge all Flashback Time Travel historical data:
BEGIN
  DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
   (scope => 'ALL');
END;
/
Example to purge the historical Flashback Time Travel data before a specified timestamp:
BEGIN
  DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
   (scope => 'TIMESTAMP', before_timestamp => '12-JUL-2023 10:24:00');
END;
/
Example to purge Flashback Time Travel historical data older than 1 day:
BEGIN
  DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
   (scope => 'TIMESTAMP', before_timestamp => SYSTIMESTAMP - INTERVAL '1' DAY);
END;
/
Example to purge the historical Flashback Time Travel data before the specified scn: 
BEGIN
  DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
   (scope => 'SCN',before_scn=> '5928826');
END;
/

See PURGE_FLASHBACK_ARCHIVE Procedure for detailed information about the procedure.

View Flashback Time Travel Information

Describes data dictionary views to view information about Flashback Time Travel files in Autonomous Database.

You can view information about Flashback Time Travel files in static data dictionary views.

View Description
*_FLASHBACK_ARCHIVE Displays information about Flashback Time Travel files.
*_FLASHBACK_ARCHIVE_TS Displays tablespaces of Flashback Time Travel files.
*_FLASHBACK_ARCHIVE_TABLES Displays information about tables that have Flashback Time Travel enabled.

Notes for Flashback Time Travel

Provides notes and restrictions for using Flashback Time Travel on Autonomous Database.

Note the following Flashback Time Travel restrictions:

  • You cannot enable Flashback Time Travel on tables with LONG data type or nested table columns.

  • You cannot enable Flashback Time Travel on a nested table, temporary table, external table, materialized view, Advanced Query (AQ) table, hybrid partitioned tables, or non-table object.

  • Flashback Time Travel does not support DDL statements that move, split, merge, or coalesce partitions or sub partitions, move tables, or convert LONG columns to LOB columns.

  • Adding or enabling a Constraint, including Foreign Key Constraint, on a table that has been enabled for Flashback Time Travel fails with ORA-55610.

    Dropping or disabling a Constraint (including Foreign Key Constraint) on a table that has been enabled for Flashback Time Travel is supported.

  • After enabling Flashback Time Travel on a table, Oracle recommends initially waiting at least 20 seconds before inserting data into the table and waiting up to 5 minutes before using Flashback Query on the table.

  • There is one Flashback Data Archive per Autonomous Database instance, named flashback_archive, and you cannot create additional Flashback Data Archives.

  • You cannot drop the Flashback Data Archive, flashback_archive, within an Autonomous Database instance.

  • You cannot create, modify, or drop tablespaces for the Flashback Data Archive. Hence, you cannot run these statements:

    • ALTER FLASHBACK ARCHIVE flashback_archive ADD TABLESPACE;
    • ALTER FLASHBACK ARCHIVE flashback_archive MODIFY TABLESPACE;
    • ALTER FLASHBACK ARCHIVE flashback_archive REMOVE TABLESPACE;

  • If you enable Flashback Time Travel on a table, but Automatic Undo Management (AUM) is disabled, error ORA-55614 occurs when you try to modify the table.

  • To enable Flashback Time Travel on a table, the table cannot use any of the following Flashback Time Travel reserved words as column names:
    • STARTSCN
    • ENDSCN
    • RID
    • XID
    • OP
    • OPERATION