Oracle DBMS_FLASHBACK_ARCHIVE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Support user applications' schema evolution during application upgrade and other table maintenance tasks beyond DDL statements supported by Flashback Data Archive.

Flashback archive is supported in 12c but not in the multi-tenant CDB architecture. We will have to wait for 12.2 it appears.
AUTHID CURRENT_USER
Constants
Name Data Type Value
NODROP BINARY_INTEGER 1
NOCOMMIT BINARY_INTEGER 2
NODELETE BINARY_INTEGER 4
Dependencies
DBMS_FDA_LIB SYS_FBA_CONTEXT_AUD SYS_FBA_TSFA
SQLOBJ$AUXDATA SYS_FBA_CONTEXT_LIST SYS_FBA_USERS
SYS_FBA_APP SYS_FBA_DL SYS_MFBA_NCHANGE
SYS_FBA_APP_TABLES SYS_FBA_FA SYS_MFBA_NROW
SYS_FBA_BARRIERSCN SYS_FBA_PARTITIONS SYS_MFBA_NTCRV
SYS_FBA_COLS SYS_FBA_PERIOD SYS_MFBA_STAGE_RID
SYS_FBA_CONTEXT SYS_FBA_TRACKEDTABLES SYS_MFBA_TRACKED_TXN
Documented Yes
Exceptions
Error Code Reason
ORA-06512 Unable to disassociate or re-associate Flashback Data Archive table <schema_name>.<table_name>
ORA-55602 The table "<schema_name"."<table_name>" is not enabled for Flashback Archive
ORA-55603 Invalid flashback archive or valid time period command
ORA-55634 Flashback Data Archive enabled table "string"."string" has different definition from its history table
ORA-55636 Flashback Data Archive enabled table "string"."string" has different definition from its history table
ORA-55637 Flashback Data Archive enabled table <schema_name>.<table_name> is not in the correct compliance
First Available 11.2.0.1
Related System Privileges FLASHBACK ARCHIVE ADMINISTER is required to import user generated history, set context level, and tamper-proof tables.
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmstran.sql
Subprograms
 
ADD_TABLE_TO_APPLICATION (new 12.1)
Takes an application name and marks a table in it as a security table dbms_flashback_archive.add_table_to_application(
application_name IN VARCHAR2,
table_name       IN VARCHAR2,
schema_name      IN VARCHAR2 DEFAULT '');
See REGISTER_APPLICATION Demo Below
 
CREATE_TEMP_HISTORY_TABLE (new 12.1)
Creates a table called TEMP_HISTORY with the correct definition in schema dbms_flashback_archive.create_temp_history_table(
owner_name1 IN VARCHAR2,
table_name1 IN VARCHAR2);
conn sys@pdbdev as sysdba

CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10M
RETENTION 30 DAY;
CREATE FLASHBACK ARCHIVE uw_archive
*
ERROR at line 1:
ORA-65131: The feature Flashback Data Archive is not supported in a pluggable database.
 
DISABLE_APPLICATION (new 12.1)
Takes an application name and marks a table in it as a security table dbms_flashback_archive.disable_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Below
 
DISABLE_ASOF_VALID_TIME (new 12.1)
Disables session level valid-time flashback dbms_flashback_archive.disable_asof_valid_time;
exec dbms_flashback_archive.disable_asof_valid_time;
 
DISASSOCIATE_FBA
Disassociates the given table from the flashback data archive dbms_flashback_archive.open_fba(
owner_name IN VARCHAR2,
table_name IN VARCHAR2);
See REASSOCIATE_FBA Demo Below
 
DROP_APPLICATION (new 12.1)
emoves an application from the applications list dbms_flashback_archive.drop_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Below
 
ENABLE_APPLICATION (new 12.1)
Takes an application name and enables flashback archive on all the security tables for this application dbms_flashback_archive.enable_application(
application_name       IN VARCHAR2,
flashback_archive_name IN VARCHAR2 DEFAILT '');
See REGISTER_APPLICATION Demo Below
 
ENABLE_AT_VALID_TIME (new 12.1)
This procedure enables session level valid time flashback dbms_flashback_archive.enable_at_valid_time(
level      IN VARCHAR2,
query_time IN TIMESTAMP DEFAULT SYSTIMESTAMP);
exec dbms_flashback_archive.enable_at_valid_time('CURRENT');
 
EXTEND_MAPPINGS (new 12.1)
Extends time mappings to times in the past dbms_flashback_archive.extend_mappings;
exec dbms_flashback_archive.extend_mappings;
 
GET_SYS_CONTEXT (new 12.1)
Gets the context previously selected by the SET_CONTEXT_LEVEL Procedure dbms_flashback_archive.get_sys_context(
xid       IN RAW,
namespace IN VARCHAR2,
parameter IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
IMPORT_HISTORY (new 12.1)
Imports history from a table called TEMP_HISTORY in the given schema 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);
TBD
 
LOCK_DOWN_APPLICATION (new 12.1)
Takes an application name and makes all the security tables read-only. The group called SYSTEM cannot be locked dbms_flashback_archive.lock_down_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Below
 
PURGE_CONTEXT (new 12.1)
Purges the context to be saved selected by SET_CONTEXT_LEVEL dbms_flashback_archive.purge_context;
See SET_CONTEXT_LEVEL Demo Below
 
REASSOCIATE_FBA
Reassociates the history table with the base table which will fail if the user has not performed corresponding structural modifications (through DDL statements) to the history table. dbms_flashback_archive.reassociate_fba(
owner_name IN VARCHAR2,
table_name IN VARCHAR2);
ALTER SYSTEM SET undo_retention = 1 SCOPE=MEMORY;

CREATE FLASHBACK ARCHIVE uw_archive
TABLESPACE uwdata
QUOTA 10M
RETENTION 30 DAY;

ALTER TABLE servers FLASHBACK ARCHIVE uw_archive;

-- perform DML on the servers table until flashback archive tables are created
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'SYS_FBA%';

DELETE FROM SYS_FBA_HIST_73776
WHERE srvr_id = 515;

exec dbms_flashback_archive.disassociate_fba('UWCLASS', 'SERVERS');

DELETE FROM SYS_FBA_HIST_73776
WHERE srvr_id = 515;

exec dbms_flashback_archive.reassociate_fba('UWCLASS', 'SERVERS');

-- make the corresponding change to the fba table
exec dbms_flashback_archive.reassociate_fba('UWCLASS', 'SERVERS');
 
REGISTER_APPLICATION (new 12.1)
Takes an application name and optionally a flashback archive, and registers an application for database hardening dbms_flashback_archive.register_application(
application_name       IN VARCHAR2,
flashback_archive_name IN VARCHAR2 DEFAULT '');
conn / as sysdba

alter session set container = PDBDEV;

exec dbms_flashback_archive.register_application('UWAPP', 'UW_ARCHIVE');

SELECT *
FROM sys_fba_app;

SELECT *
FROM sys_fba_app_tables;

exec dbms_flashback_archive.add_table_to_application('UWAPP', 'SERVERS', 'UWCLASS');

SELECT *
FROM sys_fba_app_tables;

exec dbms_flashback_archive.lock_down_application('UWAPP');

exec dbms_flashback_archive.unlock_application('UWAPP');

exec dbms_flashback_archive.disable_application('UWAPP');

exec dbms_flashback_archive.enable_application('UWAPP', 'SERVERS', 'UWCLASS');

exec dbms_flashback_archive.remove_table_from_application('UWAPP', 'SERVERS', 'UWCLASS');

SELECT *
FROM sys_fba_app_tables;
 
REMOVE_TABLE_FROM_APPLICATION (new 12.1)
Takes an application name and marks a table in it as no longer being a security table dbms_flashback_archive.remove_table_from_application (
application_name IN VARCHAR2,
table_name       IN VARCHAR2,
schema_name      IN VARCHAR2 DEFAULT '');
See REGISTER_APPLICATION Demo Above
 
SET_CONTEXT_LEVEL (new 12.1)
Defines how much of the user context is to be saved dbms_flashback_archive.set_context_level(level IN VARCHAR2);

-- valid values are ALL, TYPICAL, and NONE
exec dbms_flashback_archive.set_context_level('ALL');

exec dbms_flashback_archive.purge_context;

-- I have not yet found where any of this is saved in the data dictionary ... it is not in the SYS_FBA_CONTEXT tables
 
UNLOCK_APPLICATION (new 12.1)
Reverses a lock down performed with LOCK_DOWN_APPLICATION dbms_flashback_archive.unlock_application(application_name IN VARCHAR2);
See REGISTER_APPLICATION Demo Above

Related Topics
Flashback
Flashback Archive
Packages
Temporal Validity
Total Recall

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved