Pickleball spielen

This article highlights some 12c changes of Flashback Data Archive (FDA). See here for an introduction. Remarkably, the feature is now included in all editions. I will focus here on the 12c enhancement that an application can be registered to FDA. That has two major benefits:

1. FDA can be turned on and off for all tables of that application with one command

2. Protection (better than READ ONLY) can be turned on and off for all tables of that application with one command

The playing field:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.
SQL> create tablespace flatbs datafile '/home/oracle/prima/flatbs01.dbf' size 50m;

Tablespace created.

SQL> create table t1 as select * from dual;

Table created.

SQL> create table t2 as select * from dual;

Table created.

SQL> create flashback archive fla_10y tablespace flatbs retention 10 year;

Flashback archive created.

So far no new feature as the same can be done with 11g. No I register the application – that is just declarative:

SQL> begin
     dbms_flashback_archive.register_application
     (application_name=>'MYAPP',
     flashback_archive_name=>'FLA_10Y');
     end;
     /  

PL/SQL procedure successfully completed.

Now I add all tables of that application:

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t1',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t2',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

I can now turn FDA on and off for all the tables with one command:

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

SQL> exec dbms_flashback_archive.enable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

TABLE_NAME STATUS
---------- ----------
T1         ENABLED
T2         ENABLED

SQL> exec dbms_flashback_archive.disable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

Furthermore, I can now protect these tables with one command against modification and drop:

SQL> exec dbms_flashback_archive.lock_down_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> delete from t1;
delete from t1
            *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

This is a much better protection than just making tables READ ONLY:

SQL> create table t3 as select * from dual;

Table created.

SQL> alter table t3 read only;

Table altered.

SQL> drop table t3;

Table dropped.

Please notice also that the tables need not to be in FDA mode to be protected by the lock_down_application call. The doc by the way doesn’t mention the unlock procedure:

SQL> exec dbms_flashback_archive.unlock_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

Conclusion: The 12c enhancements of Flashback Data Archive enable the fast change of the FDA mode for multiple tables. Furthermore, it is now easily possible to prevent tables from being modified and dropped.