Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E16760-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

69 DBMS_FLASHBACK_ARCHIVE

The DBMS_FLASHBACK_ARCHIVE package contains two simple procedures for disassociation and reassociation of a Flashback Data Archive (FDA) enabled table from/with its underlying FDA respectively.

See Also:

Oracle Database Advanced Application Developer's Guide

This chapter contains the following topics:


Using DBMS_FLASHBACK_ARCHIVE


Overview

The Flashback Data Archive feature, which is available through the Total Recall option, provides strict protection on the internal history tables that it creates and maintains for users.

The read-only semantics provided prohibits users, including a DBA, from doing updates, deletes, and inserts on the Flashback Data Archive internal history tables. The feature also prohibits users from issuing any DDL statements on these tables. This strict security enforcement by Flashback Data Archive meets the requirements of regulatory-compliance type of applications. The feature supports a lot of common DDL statements, including some DDL statements that alter table definition or incur data movement, on user tables that are enabled for Flashback Data Archive. However, there are some DDL statements that are not supported on tables enabled for the feature. Since most applications schemas evolve as they release new versions of their software, the ability to perform DDL operations on the base table is a key customer requirement.

In order to support user applications' schema evolution during application upgrade and other table maintenance tasks beyond DDL statements supported by Flashback Data Archive, the DBMS_FLASHBACK_ARCHIVE package provides a set of simple-to-use PL/SQL procedures:

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.


Security Model

Users with the FLASHBACK ARCHIVE ADMINISTER system privilege can execute the disassociation and reassociation PL/SQL procedures. Once a table is disassociated, normal users can perform DDL and DML statements as long as they have the necessary privileges on the tables.


Examples

Normally, users cannot perform any modification to the history table:

SQL> DELETE FROM scott.SYS_FBA_HIST_61527;

ERROR at line 1: 
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_HIST_61527"

Users also cannot issue DDL statements on history tables:

SQL> ALTER TABLE scott.SYS_FBA_HIST_61527 DROP COLUMN comm;

ERROR at line 1: 
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_HIST_61527"

Use the DISASSOCIATE_FBA Procedure to disassociate the scott.emp_test table:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('scott','emp_test');

PL/SQL procedure successfully completed. 

Now a user can perform table structural modifications (through DDL statements) to the user base table:

SQL> ALTER TABLE scott.emp_test RENAME COLUMN sal TO salary;

Table altered.

Users can also modify the contents in the history table that they couldn't modify previously.

SQL> DELETE FROM scott.SYS_FBA_HIST_61527 WHERE empno=3968;

2 rows deleted. 

If a user tries to reassociate the history table with the base table, this will fail as the user has not performed corresponding structural modifications (through DDL statements) to the history table:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp_test');
BEGIN DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp_test');
END;

ERROR at line 1:
ORA-55636: Flashback Data Archive enabled table "SCOTT"."EMP_TEST" has different definition from its history table
ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 17
ORA-06512: at line 1

Disassociate the table and fix the table definition problem:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('scott','emp_test');

PL/SQL procedure successfully completed.

Perform the same rename column DDL on the history table to make its definition conform to its base table scott.emp_test:

SQL> ALTER TABLE scott.SYS_FBA_HIST_61527 RENAME COLUMN sal TO salary;

Table altered.

Finally, reassociate the base table with its history table successfully:

SQL> EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('scott','emp_test')

PL/SQL procedure successfully completed.

Summary of DBMS_FLASHBACK_ARCHIVE Subprograms

Table 69-1 DBMS_FLASHBACK_ARCHIVE Package Subprograms

Subprogram Description

DISASSOCIATE_FBA Procedure

Disassociates the given table from the flashback data archive

REASSOCIATE_FBA Procedure

Reassociates the given table with the flashback data archive



DISASSOCIATE_FBA Procedure

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

Syntax

DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA (
   owner_name      VARCHAR2, 
   table_name      VARCHAR2);

Parameters

Table 69-2 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 69-3 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



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 69-4 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 69-5 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