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.
- 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. - Enable Flashback Time Travel for a Table
Describes steps to enable Flashback Time Travel for a table in Autonomous Database. - Disable Flashback Time Travel for a Table
Describes procedure to disable Flashback Time Travel for a table in Autonomous Database. - Modify the Retention Time for Flashback Time Travel
Describes procedure to modify the retention time for Flashback Time Travel in Autonomous Database. - Purge Historical Data for Flashback Time Travel
Describes procedure to purge historical data for Flashback Time Travel in Autonomous Database. - View Flashback Time Travel Information
Describes data dictionary views to view information about Flashback Time Travel files in Autonomous Database. - Notes for Flashback Time Travel
Provides notes and restrictions for using Flashback Time Travel on Autonomous Database.
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 forflashback_archive
. By default theADMIN
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.
Parent topic: Track Table Changes with Flashback Time Travel
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.
-
You must be logged in as the
ADMIN
user or have theFLASHBACK ARCHIVE
object privilege.The
ADMIN
user or the user having theFLASHBACK ARCHIVE
privilegeWITH GRANT OPTION
can grant theFLASHBACK 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.
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.
Parent topic: Track Table Changes with Flashback Time Travel
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.
employee
table:ALTER TABLE employee NO FLASHBACK ARCHIVE;
Parent topic: Track Table Changes with Flashback Time Travel
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
.
BEGIN
DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION
(retention_days => 365);
END;
/
See SET_FLASHBACK_ARCHIVE_RETENTION Procedure for detailed information about the procedure.
Parent topic: Track Table Changes with Flashback Time Travel
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
.
BEGIN
DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
(scope => 'ALL');
END;
/
BEGIN
DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
(scope => 'TIMESTAMP', before_timestamp => '12-JUL-2023 10:24:00');
END;
/
BEGIN
DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
(scope => 'TIMESTAMP', before_timestamp => SYSTIMESTAMP - INTERVAL '1' DAY);
END;
/
BEGIN
DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
(scope => 'SCN',before_scn=> '5928826');
END;
/
See PURGE_FLASHBACK_ARCHIVE Procedure for detailed information about the procedure.
Parent topic: Track Table Changes with Flashback Time Travel
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. |
Parent topic: Track Table Changes with Flashback Time Travel
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 toLOB
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
Parent topic: Track Table Changes with Flashback Time Travel