DBMS_FLASHBACK

73 DBMS_FLASHBACK_ARCHIVE

The DBMS_FLASHBACK_ARCHIVE package contains procedures for performing various flashback tasks.

These include:

Disassociation and reassociation of a Flashback Archive enabled table from/with its underlying Flashback Data Archive (FDA)
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

73.1 DBMS_FLASHBACK_ARCHIVE Overview

Flashback Data Archive (FDA) 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 FDA history within the same database only, and not outside it. This package does not allow you to move FDA table data. It allows you to import customer history only and not FDA history.

The read-only semantics prohibit users, including a DBA, from doing updates, deletes, and inserts on the Flashback Data Archive 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 Data Archive 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 Data Archive, 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 FDA
To reassociate a temporarily disassociated base table with its underlying FDA

After a user has disassociated the base table from its FDA, it's possible to issue any DDL statements on the base table or the history tables in the FDA. Having finished with the schema changes, the user can then reassociate the base table with its FDA so that Flashback Data Archive protection is in operation and automatic tracking and archiving is resumed.

73.2 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.

73.3 DBMS_FLASHBACK_ARCHIVE Constants

The DBMS_FLASHBACK_ARCHIVE package uses the constants shown in the following table.

Table 73-1 DBMS_FLASHBACK_ARCHIVE Constants

Constant	Type	Value	Description
`NODROP`	`BINARY_INTEGER`	`1`	Do not drop temporary history table
`NOCOMMIT`	`BINARY_INTEGER`	`2`	Do not commit transaction
`NODELETE`	`BINARY_INTEGER`	`4`	Do not delete data in history table

73.4 Summary of DBMS_FLASHBACK_ARCHIVE Subprograms

This table lists the DBMS_FLASHBACK_ARCHIVE subprograms and briefly describes them.

Table 73-2 DBMS_FLASHBACK_ARCHIVE Package Subprograms

Subprogram	Description
ADD_TABLE_TO_APPLICATION Procedure	Takes an application name and adds a table to the application as a security table
CREATE_TEMP_HISTORY_TABLE Procedure	Creates a table called `TEMP_HISTORY` with the correct definition in schema
DISABLE_APPLICATION Procedure	Takes an application name and marks a table in it as a security table
DISABLE_ASOF_VALID_TIME Procedure	Disables session level valid-time flashback
DISASSOCIATE_FBA Procedure	Disassociates the given table from the flashback data archive
DROP_APPLICATION Procedure	Takes an application name and removes it from the list of applications
ENABLE_APPLICATION Procedure	Takes an application name and enables Flashback Data Archive on all the security tables for this application
ENABLE_AT_VALID_TIME Procedure	Enables session level valid time flashback
EXTEND_MAPPINGS Procedure	Extends time mappings to times in the past
GET_SYS_CONTEXT Function	Gets the context previously selected by the SET_CONTEXT_LEVEL Procedure
IMPORT_HISTORY Procedure	Imports history from a table called `TEMP_HISTORY` in the given schema.
LOCK_DOWN_APPLICATION Procedure	Takes an application name and makes all the security tables read-only. The group called `SYSTEM` cannot be locked
PURGE_CONTEXT Procedure	Purges the context to be saved selected by the SET_CONTEXT_LEVEL Procedure
REASSOCIATE_FBA Procedure	Reassociates the given table with the flashback data archive
REGISTER_APPLICATION Procedure	Takes an application name and optionally a Flashback Data Archive, and registers an application for database hardening
REMOVE_TABLE_FROM_APPLICATION Procedure	Takes an application name and marks a table in it as no longer being a security table
SET_CONTEXT_LEVEL Procedure	Defines how much of the user context is to be saved

73.4.1 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 Data Archive, then this table will also be enabled for Flashback Data Archive.

Syntax

DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (
   application_name           IN   VARCHAR2,
   table_name                 IN   VARCHAR2,
   schema_name                IN   VARCHAR2 := NULL);

Parameters

Table 73-3 ADD_TABLE_TO_APPLICATION Procedure Parameters

Parameter	Description
`application_name`	Name of the application for which a table has been added as a security table
`table_name`	Name of the table to add as a security table for the given application
`schema_name`	Name of the schema containing the desired table. If no schema name is specified, the current schema is used.

73.4.2 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 73-4 CREATE_TEMP_HISTORY_TABLE Procedure Parameters

Parameter	Description
`owner_name`1	Schema of the Flashback Data Archive-enabled table
`table_name`1	Name of the Flashback Data Archive-enabled table

73.4.3 DISABLE_APPLICATION Procedure

This procedure takes an application name and disables Flashback Data Archive on all of its security tables.

Syntax

DBMS_FLASHBACK_ARCHIVE.DISABLE_APPLICATION (
   application_name           IN   VARCHAR2);

Parameters

Table 73-5 DISABLE_APPLICATION Procedure Parameters

Parameter	Description
`application_name`	Name of the application whose security tables will be disabled for Flashback Data Archive

73.4.4 DISABLE_ASOF_VALID_TIME Procedure

This procedure disables session level valid-time flashback.

Syntax

DBMS_FLASHBACK_ARCHIVE.DISABLE_ASOF_VALID_TIME;

73.4.5 DISASSOCIATE_FBA Procedure

This procedure disassociates the given table from the flashback data archive.

Syntax

DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA (
   owner_name     IN    VARCHAR2, 
   table_name     IN    VARCHAR2);

Parameters

Table 73-6 DISASSOCIATE_FBA Procedure Parameters

Parameter	Description
`owner_name`	Schema of the Flashback Data Archive enabled base table
`table_name`	Name of the Flashback Data Archive enabled base table

Exceptions

Table 73-7 DISASSOCIATE_FBA Procedure Exceptions

Parameter	Description
`ORA-55602`	User table is not enabled for Flashback Data Archive
`ORA-55634`	Cannot acquire the lock on the table for disassociation

73.4.6 DROP_APPLICATION Procedure

This procedure takes an application name and removes it from the list of applications. As part of this procedure, Flashback Data Archive 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 73-8 DROP_APPLICATION Procedure Parameters

Parameter	Description
`application_name`	Name of the application for which a table has been added as a security table

73.4.7 ENABLE_APPLICATION Procedure

This procedure takes an application name and enables Flashback Data Archive on all the security tables for this application. Once an application is enabled, every change to an FDA enabled table will be tracked.

Syntax

DBMS_FLASHBACK_ARCHIVE.ENABLE_APPLICATION (
   application_name           IN   VARCHAR2);

Parameters

Table 73-9 ENABLE_APPLICATION Procedure Parameters

Parameter	Description
`application_name`	Name of the application for which to enable Flashback Data Archive on all its security tables

73.4.8 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 73-10 ENABLE_AT_VALID_TIME Procedure Parameters

Parameter Description

Parameter	Description
`level`	Options: `All` - Sets the visibility of temporal data to the full table, which is the default temporal table visibility `CURRENT` - Sets the visibility of temporal data to currently valid data within the valid time period at the session level `ASOF` - Sets the visibility of temporal data to data valid as of the given time as defined by the timestamp
`query_time`	Used only if level is `ASOF`. Data which is valid at this `query_time` will only be shown.

level

Options:

All - Sets the visibility of temporal data to the full table, which is the default temporal table visibility
CURRENT - Sets the visibility of temporal data to currently valid data within the valid time period at the session level
ASOF - Sets the visibility of temporal data to data valid as of the given time as defined by the timestamp

query_time

Used only if level is ASOF. Data which is valid at this query_time will only be shown.

73.4.9 EXTEND_MAPPINGS Procedure

This procedure extends time mappings to times in the past.

Syntax

DBMS_FLASHBACK_ARCHIVE.EXTEND_MAPPINGS;

73.4.10 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 73-11 GET_SYS_CONTEXT Function Parameters

Parameter	Description
`xid`	Transaction identifier is an opaque handle to a transaction obtained from the versions query
`namespace`	Namespace
`parameter`	If undefined, the subprogram returns `NULL`

Related Topics

SET_CONTEXT_LEVEL Procedure

73.4.11 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.

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 73-12 IMPORT_HISTORY Procedure Parameters

Parameter	Description
`owner_name`1	Schema of the Flashback Data Archive-enabled table
`table_name`1	Name of the Flashback Data Archive-enabled table
`temp_history_name`	Optional temporary history table from which we import history data
`options`	One (or a combination) of constants (`NODROP`, `NOCOMMIT`, and `NODELETE`) to specify if we want to drop, commit changes of, or truncate the temporary history table

Usage Notes

The database function TIMESTAMP_TO_SCN can be used to convert times to SCN when populating the temporary history table.

Related Topics

CREATE_TEMP_HISTORY_TABLE Procedure

73.4.12 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 73-13 LOCK_DOWN_APPLICATION Procedure Parameters

Parameter	Description
`application_name`	Name of the application for which a table has been added as a security table

73.4.13 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

SET_CONTEXT_LEVEL Procedure

73.4.14 REASSOCIATE_FBA Procedure

This procedure reassociates the given table with the flashback data archive.

Syntax

DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA (
   owner_name      VARCHAR2, 
   table_name      VARCHAR2);

Parameters

Table 73-14 REASSOCIATE_FBA Procedure Parameters

Parameter	Description
`owner_name`	Schema of the Flashback Data Archive enabled base table
`table_name`	Name of the Flashback Data Archive enabled base table

Exceptions

Table 73-15 REASSOCIATE_FBA Procedure Exceptions

Parameter	Description
`ORA-55602`	User table is not enabled for Flashback Data Archive
`ORA-55636`	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 FDA internal history table schema has some row versions metadata columns. The procedure will signal an error if any metadata column is dropped by users.

73.4.15 REGISTER_APPLICATION Procedure

This procedure takes an application name and optionally a Flashback Data 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 Data Archive using the given Flashback Data Archive. If no Flashback Data Archive is specified, the default Flashback Data Archive is used.

Parameter	Description
`application_name`	Name of the application which is being registered. The application `SYSTEM` is already registered when the package is created and is populated with list of tables needed for database hardening.
`flashback_archive_name`	Name of the Flashback Data Archive in which the historical data for the security tables for given application is stored. If no Flashback Data Archive is specified, the default Flashback Data Archive is used.

73.4.16 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 Data Archive, Flashback Data 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 73-17 REMOVE_TABLE_FROM_APPLICATION Procedure Parameters

Parameter	Description
`application_name`	Name of the application for which a table is being removed from the list of security tables
`table_name`	Name of the table to mark as being no longer a security table for the given application
`schema_name`	Name of the schema containing the desired table. If no schema name is specified, the current schema is used.

73.4.17 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 73-18 SET_CONTEXT_LEVEL Procedure Parameters

Parameter Description

Parameter	Description
`level`	Depending on how much of the user context needs to be saved: `ALL` - the entire `SYS_CONTEXT` `TYPICAL` - the user ID, global user ID and the hostname `NONE` - nothing

level

Depending on how much of the user context needs to be saved:

ALL - the entire SYS_CONTEXT
TYPICAL - the user ID, global user ID and the hostname
NONE - nothing