79 DBMS_FLASHBACK_ARCHIVE
The DBMS_FLASHBACK_ARCHIVE package contains procedures for
performing various flashback archive tasks.
These include:
-
Disassociation and reassociation of a Flashback Archive enabled table from/with its underlying Flashback Time Travel
-
Tamper-proofing the tables of an application
-
Importing of user history
Caution:
Importing user-generated history can lead to inaccurate, or unreliable results. This procedure should only be used after consulting with Oracle Support.
-
Enabling and disabling of session-level support for valid-time
See Also:
Oracle Database Development Guide for more information about Using Flashback Time Travel
This chapter contains the following topics:
DBMS_FLASHBACK_ARCHIVE Overview
Flashback Time Travel provides strict protection on the internal history tables that it creates and maintains for users.
Caution:
The DBMS_FLASHBACK_ARCHIVE package does not support
migration between databases. It allows you to move Flashback Time Travel history
within the same database only, and not outside it. This package does not allow
you to move Flashback Time Travel table data. It allows you to import customer
history only and not Flashback Time Travel history.
The read-only semantics prohibit users, including a DBA, from doing updates, deletes, and inserts on the Flashback Time Travel internal history tables. Users are also prevented from issuing any DDL statements on these tables. This strict security enforcement helps meet the requirements of applications in regulatory / compliance environments. Flashback Time Travel supports most common DDL statements, including those that alter the table definition or incur data movement. However, some DDL statements are not supported on Flashback Archive enabled tables. Since most application schemas are modified during application software upgrades, the ability to perform DDL operations on tracked tables is critical.
To support schema evolution during application upgrades and other table maintenance
tasks that require use of DDL statements not supported by Flashback Time Travel, the
DBMS_FLASHBACK_ARCHIVE package provides a set of simple-to-use
PL/SQL procedures:
-
To disassociate a Flashback Archive enabled base table from the underlying Flashback Time Travel
-
To reassociate a temporarily disassociated base table with its underlying Flashback Time Travel
After a user has disassociated the base table from its Flashback Archive, it's possible to issue any DDL statements on the base table or the history tables in the Flashback Archive. Having finished with the schema changes, the user can then reassociate the base table with its Flashback Archive so that Flashback Time Travel protection is in operation and automatic tracking and archiving is resumed.
DBMS_FLASHBACK_ARCHIVE Security Model
Users need the FLASHBACK_ARCHIVE_ADMINISTER privilege to import user-generated history, to set context level, and to tamper-proof tables. After a table is disassociated, users can perform DDL and DML statements on the table if they have the necessary privileges. Enabling and disabling session-level Valid Time Temporal flashback needs no additional privileges.
DBMS_FLASHBACK_ARCHIVE Constants
The DBMS_FLASHBACK_ARCHIVE package uses the constants shown in the following table.
Table 79-1 DBMS_FLASHBACK_ARCHIVE Constants
| Constant | Type | Value | Description |
|---|---|---|---|
|
|
|
|
Do not drop temporary history table |
|
|
|
|
Do not commit transaction |
|
|
|
|
Do not delete data in history table |
Summary of DBMS_FLASHBACK_ARCHIVE Subprograms
This table lists the DBMS_FLASHBACK_ARCHIVE subprograms and briefly describes them.
Table 79-2 DBMS_FLASHBACK_ARCHIVE Package Subprograms
| Subprogram | Description |
|---|---|
|
Takes an application name and adds a table to the application as a security table |
|
|
Creates a table called |
|
|
Takes an application name and marks a table in it as a security table |
|
|
Disables session level valid-time flashback |
|
|
Disassociates the given table from the Flashback Time Travel |
|
|
Takes an application name and removes it from the list of applications |
|
|
Takes an application name and enables Flashback Time Travel on all the security tables for this application |
|
|
Enables session level valid time flashback |
|
|
Extends time mappings to times in the past |
|
| GET_CURRENT_LIFESPAN_DIGEST Function | Generates the current lifespan digest for the specified row in a user table |
|
Gets the context previously selected by the SET_CONTEXT_LEVEL Procedure |
|
|
Imports history from a table called |
|
|
Takes an application name and makes all the security tables read-only. The group called |
|
|
Purges the context to be saved selected by the SET_CONTEXT_LEVEL Procedure |
|
|
Reassociates the given table with the Flashback Time Travel |
|
|
Takes an application name and optionally a Time Travel, and registers an application for database hardening |
|
|
Takes an application name and marks a table in it as no longer being a security table |
|
|
Defines how much of the user context is to be saved |
|
| VERIFY_BLOCKCHAIN_LIFESPAN Procedure | Verifies the content of the current, historical, or all lifespans of user table rows |
ADD_TABLE_TO_APPLICATION Procedure
This procedure takes an application name and adds a table to the application as a security table. If the application is enabled for Flashback Time Travel, then this table will also be enabled for Flashback Time Travel.
Syntax
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION ( application_name IN VARCHAR2, table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL);
Parameters
Table 79-3 ADD_TABLE_TO_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application for which a table has been added as a security table |
|
|
Name of the table to add as a security table for the given application |
|
|
Name of the schema containing the desired table. If no schema name is specified, the current schema is used. |
CREATE_TEMP_HISTORY_TABLE Procedure
This procedure creates a table called TEMP_HISTORY with the correct definition in schema.
Syntax
DBMS_FLASHBACK_ARCHIVE.CREATE_TEMP_HISTORY_TABLE ( owner_name1 IN VARCHAR2, table_name1 IN VARCHAR2);
Parameters
Table 79-4 CREATE_TEMP_HISTORY_TABLE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of the Flashback Time Travel-enabled table |
|
|
Name of the Flashback Time Travel-enabled table |
DISABLE_APPLICATION Procedure
This procedure takes an application name and disables Flashback Time Travel on all of its security tables.
Syntax
DBMS_FLASHBACK_ARCHIVE.DISABLE_APPLICATION ( application_name IN VARCHAR2);
Parameters
Table 79-5 DISABLE_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application whose security tables will be disabled for Flashback Time Travel |
DISABLE_ASOF_VALID_TIME Procedure
This procedure disables session level valid-time flashback.
Syntax
DBMS_FLASHBACK_ARCHIVE.DISABLE_ASOF_VALID_TIME;
DISASSOCIATE_FBA Procedure
This procedure disassociates the given table from the Flashback Time Travel.
Syntax
DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA ( owner_name IN VARCHAR2, table_name IN VARCHAR2);
Parameters
Table 79-6 DISASSOCIATE_FBA Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of the Flashback Time Travel enabled base table |
|
|
Name of the Flashback Time Travel enabled base table |
Exceptions
Table 79-7 DISASSOCIATE_FBA Procedure Exceptions
| Exception | Description |
|---|---|
|
|
User table is not enabled for Flashback Time Travel |
|
|
Cannot acquire the lock on the table for disassociation |
DROP_APPLICATION Procedure
This procedure takes an application name and removes it from the list of
applications. As part of this procedure, Flashback Time Travel will be disabled on all
security-enabled tables and all history data will be lost. The group called
SYSTEM cannot be dropped.
Syntax
DBMS_FLASHBACK_ARCHIVE.DROP_APPLICATION ( application_name IN VARCHAR2);
Parameters
Table 79-8 DROP_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application for which a table has been added as a security table |
ENABLE_APPLICATION Procedure
This procedure takes an application name and enables Flashback Time Travel on all the security tables for this application. Once an application is enabled, every change to an Flashback Time Travel enabled table will be tracked.
Syntax
DBMS_FLASHBACK_ARCHIVE.ENABLE_APPLICATION ( application_name IN VARCHAR2);
Parameters
Table 79-9 ENABLE_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application for which to enable Flashback Time Travel on all its security tables |
ENABLE_AT_VALID_TIME Procedure
This procedure enables session level valid time flashback.
Syntax
DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME ( level IN VARCHAR2, query_time IN TIMESTAMP DEFAULT SYSTIMESTAMP);
Parameters
Table 79-10 ENABLE_AT_VALID_TIME Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Options:
|
|
|
Used only if level is |
EXTEND_MAPPINGS Procedure
This procedure extends time mappings to times in the past.
Syntax
DBMS_FLASHBACK_ARCHIVE.EXTEND_MAPPINGS;
GET_CURRENT_LIFESPAN_DIGEST Function
This function generates the current lifespan digest for the specified row in a user table.
Syntax
DBMS_FLASHBACK_ARCHIVE.GET_CURRENT_LIFESPAN_DIGEST (
rid IN VARCHAR2,
start_scn IN RAW,
xid IN RAW,
op IN VARCHAR2,
objno IN NUMBER)
RETURN RAW;Parameters
Table 79-11 GET_CURRENT_LIFESPAN_DIGEST Function Parameters
| Parameter | Description |
|---|---|
|
|
The ROWID of the user table row. |
|
|
The latest start SCN of the row. |
|
|
The latest transaction ID of the row. |
op |
The latest operation that is performed on the row. |
objno |
The table's object number. |
The cryptographic hash is generated over all column values in the ascending order of the column IDs. Each column value is prefixed by column metadata. For more information, refer to the Appendix B.1 Column Content in Blockchain Tables section of the Database Administrator’s Guide.
-
Each row of the user table transitions through a series of "lifespans" created by transactions that commit changes to the row. The most recent lifespan is referred to as the "current lifespan," and all other previous lifespans are referred to as "historical lifespans."
-
The historical lifespans are recorded into a Flashback Archive (FBA) internal table:
SYS_FBA_HIST_<objno>. -
The current lifespans are always present in the user table. However, the relevant metadata of current lifespans are recorded in another FBA internal table:
SYS_FBA_TCRV_<objno>. -
This API can be used to compute the current lifespan digest of the specific row of a user table using its associated metadata from the FBA internal table:
SYS_FBA_TCRV_<objno>. A user can record this digest for detecting corruptions, if any, in the row data.
GET_SYS_CONTEXT Function
This function gets the context previously selected by the SET_CONTEXT_LEVEL Procedure.
Syntax
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT ( xid IN RAW, namespace IN VARCHAR2, parameter IN VARCHAR2) RETURN VARCHAR2;
Parameters
Table 79-12 GET_SYS_CONTEXT Function Parameters
| Parameter | Description |
|---|---|
|
|
Transaction identifier is an opaque handle to a transaction obtained from the versions query |
|
|
Namespace |
|
|
If undefined, the subprogram returns |
Related Topics
IMPORT_HISTORY Procedure
This procedure is called after invoking the CREATE_TEMP_HISTORY_TABLE procedure, and after the TEMP_HISTORY table is populated with user-generated history data
Caution:
Importing user-generated history can lead to inaccurate, or unreliable results. This procedure should only be used after consulting with Oracle Support.
The DBMS_FLASHBACK_ARCHIVE package does not support
migration between databases. It allows you to move Flashback Time Travel history
within the same database only, and not outside it. This package does not allow you to
move Flashback Time Travel table data. It allows you to import customer history only
and not Flashback Time Travel history.
Syntax
DBMS_FLASHBACK_ARCHIVE.IMPORT_HISTORY ( owner_name1 IN VARCHAR2, table_name1 IN VARCHAR2 temp_history_name IN VARCHAR2 DEFAULT 'TEMP_HISTORY', options IN BINARY_INTEGER DEFAULT 0);
Parameters
Table 79-13 IMPORT_HISTORY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of the Flashback Time Travel-enabled table |
|
|
Name of the Flashback Time Travel-enabled table |
|
|
Optional temporary history table from which we import history data |
|
|
One (or a combination) of constants ( |
Usage Notes
The database function TIMESTAMP_TO_SCN can be used to convert times to SCN when populating the temporary history table.
Related Topics
LOCK_DOWN_APPLICATION Procedure
This procedure takes an application name and makes all the security tables read-only. The group called SYSTEM cannot be locked.
Syntax
DBMS_FLASHBACK_ARCHIVE.LOCK_DOWN_APPLICATION ( application_name IN VARCHAR2);
Parameters
Table 79-14 LOCK_DOWN_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application for which a table has been added as a security table |
PURGE_CONTEXT Procedure
This procedure purges the context to be saved selected by the SET_CONTEXT_LEVEL Procedure.
Syntax
DBMS_FLASHBACK_ARCHIVE.PURGE_CONTEXT;
Related Topics
REASSOCIATE_FBA Procedure
This procedure reassociates the given table with the Flashback Time Travel.
Syntax
DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA ( owner_name VARCHAR2, table_name VARCHAR2);
Parameters
Table 79-15 REASSOCIATE_FBA Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Schema of the Flashback Time Travel enabled base table |
|
|
Name of the Flashback Time Travel enabled base table |
Exceptions
Table 79-16 REASSOCIATE_FBA Procedure Exceptions
| Parameter | Description |
|---|---|
|
|
User table is not enabled for Flashback Time Travel |
|
|
Table definition validation failed |
Usage Notes
-
The procedure will signal an error if the base table and the history table do not have identical data definitions. For example, when columns are added or table is split, the resulting base table and history table need to have the same schema.
-
The Flashback Time Travel internal history table schema has some row versions metadata columns. The procedure will signal an error if any metadata column is dropped by users.
REGISTER_APPLICATION Procedure
This procedure takes an application name and optionally a Flashback Archive, and registers an application for database hardening.
When database hardening is enabled, then all the security tables for that application are enabled for Flashback Archive using the given Flashback Archive. If no Flashback Archive is specified, the default Flashback Archive is used.
See Also:
Using Flashback Time Travel in Oracle Database Development Guide regarding database hardening
Syntax
DBMS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION ( application_name IN VARCHAR2, flashback_archive_name IN VARCHAR2 := NULL);
Parameters
Table 79-17 REGISTER_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application which is being registered. The application |
|
|
Name of the Flashback Archive in which the historical data for the security tables for given application is stored. If no Flashback Archive is specified, the default Flashback Archive is used. |
REMOVE_TABLE_FROM_APPLICATION Procedure
This procedure takes an application name and marks a table in it as no longer being a security table.
If the application is already enabled for Flashback Archive, Flashback Archive will be disabled for this table.
Syntax
DBMS_FLASHBACK_ARCHIVE.REMOVE_TABLE_TO_APPLICATION ( application_name IN VARCHAR2, table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL);
Parameters
Table 79-18 REMOVE_TABLE_FROM_APPLICATION Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application for which a table is being removed from the list of security tables |
|
|
Name of the table to mark as being no longer a security table for the given application |
|
|
Name of the schema containing the desired table. If no schema name is specified, the current schema is used. |
SET_CONTEXT_LEVEL Procedure
This procedure defines how much of the user context is to be saved.
Syntax
DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL ( level VARCHAR2);
Parameters
Table 79-19 SET_CONTEXT_LEVEL Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Depending on how much of the user context needs to be saved:
|
VERIFY_BLOCKCHAIN_LIFESPAN Procedure
This procedure verifies the contents of the current lifespans, historical
lifespans, or all lifespans of the user table rows that are protected using blockchain
Flashback Archive (FBA). The verification is done by comparing the row's cryptographic hash
with the ORAFBA_CURRENT_LIFESPAN_DIGEST$ column value recorded in its
previous historical lifespan.
Syntax
PROCEDURE verify_blockchain_lifespan(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
number_of_lifespan_verified OUT NUMBER,
type_of_lifespan IN NUMBER DEFAULT LIFESPAN_ALL);Parameters
Table 79-20 VERIFY_BLOCKCHAIN_LIFESPAN Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The user schema name. |
|
|
The user table name. |
|
|
The number of lifespan rows verified. |
type_of_lifespan |
The type of lifespan to be verified. The following are the constants for the
|