How to alter a flashback data archive that needs additional quota

SELECT F.FLASHBACK_ARCHIVE_NAME, F.TABLESPACE_NAME,F.QUOTA_IN_MB, 
       (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024)
    FROM DBA_SEGMENTS S 
    WHERE S.TABLESPACE_NAME=F.TABLESPACE_NAME) "occupied"
 FROM  DBA_FLASHBACK_ARCHIVE_TS F;

FLASHBACK_ARCHIVE_NAME	TABLESPACE_NAME	QUOTA_IN_MB	occupied
MY_FDA	FDA	20480	19

If you've reached the quota, add a larger one, like this:

ALTER FLASHBACK ARCHIVE MY_FDA 
MODIFY TABLESPACE FDA QUOTA 20 G; 

How to solve ORA-55622 during DROP USER ... CASCADE;

During a DROP USER ... CASCADE operation, you may encounter the following error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_TCRV_83287"

Solution: disable flashback archive:

Check the currently enabled flashback archive tables:

set lines 155
col TABLE_NAME for a30
col OWNER_NAME for a30
col FLASHBACK_ARCHIVE_NAME for a30
col ARCHIVE_TABLE_NAME for a30
col STATUS for a30
SELECT * 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');

Put the following in a script called gen_disable_fda.sql:

set trimspool on
set heading off
set feedback off
set echo off
set verify off
spool disable_flb_archive.sql
SELECT 'spool disable_flb_archive.log' FROM dual;
SELECT 'alter table ' || owner_name || '.' || table_name || ' no flashback archive;' 
FROM DBA_FLASHBACK_ARCHIVE_TABLES 
WHERE OWNER_NAME=upper('SCOTT');
SELECT 'exit' FROM dual;
exit

The script above will generate alter table statements for all SCOTT-owned tables with flashback archive enabled:

sqlplus / as sysdba @gen_disable_fda.sql

Finally, run the script disable_flb_archive.sql, which now contain statements as shown below:

alter table SCOTT.EMP no flashback archive;

After this has been done, the schema may be dropped.

How to create a Flashback Data Archive

Remember that tablespaces used for flashback data archive must use ASSM (Automatic Segment Space Managment)

Create a tablespace (here I am using Oracle Managed Files, no file specification necessary)

SQL> CREATE TABLESPACE LAST_MONTH;

Tablespace created.

SQL> CREATE TABLESPACE LAST_YEAR;

Tablespace created.

Next, create the flashback archive containers:

SQL> CREATE FLASHBACK ARCHIVE ONE_MONTH TABLESPACE LAST_MONTH RETENTION 1 MONTH;

FLASHBACK ARCHIVE CREATED.

SQL> CREATE FLASHBACK ARCHIVE ONE_YEAR TABLESPACE LAST_YEAR RETENTION 1 YEAR;

FLASHBACK ARCHIVE CREATED.


Prepare the table so it can use flashback archive:

SQL> ALTER TABLE SCOTT.CUSTOMERS ENABLE ROW MOVEMENT;

TABLE ALTERED.

Enable flashback archiving by assigning the table to the appropriate flashback archive container:

SQL> ALTER TABLE SCOTT.CUSTOMERS FLASHBACK ARCHIVE LAST_YEAR;

TABLE ALTERED.


To revers the operations:

SQL> ALTER TABLE CUSTOMERS NO FLASHBACK ARCHIVE;

TABLE ALTERED.

SQL> DROP FLASHBACK ARCHIVE ONE_YEAR;

FLASHBACK ARCHIVE DROPPED.

SQL> DROP FLASHBACK ARCHIVE ONE_MONTH;

FLASHBACK ARCHIVE DROPPED.

Useful views:

DBA_FLASHBACK_ARCHIVE

DBA_FLASHBACK_ARCHIVE_TABLES

DBA_FLASHBACK_ARCHIVE_TS

Which are the potential use cases for Flashback Data Archive, introduced in Oracle 11gR1?

Flashback Data Archive is handy for many purposes. Here are some ideas:

- To audit for recording how data changed
- To enable an application to undo changes (correct mistakes)
- To debug how data has been changed
- To comply with some regulations that require data must not be changed after some time.
Flashback Data Archives are not regular tables so they can't be changed by typical users.
- Recording audit trails on cheaper storage thereby allowing more retention at less cost


Remember: Inserts do not create archive records; because they are not changes to data.

Source: Arup Nanda

How to disable flashback archiver process, introduced to support Oracle 11gR1 feature "Flashback Data Archive"

If you do not use the flashback data archive feature, and have no plans to do so, you might as well disable the process, even though it is defined as "lightweight".

First, confirm that the operating system process is running:

$ ps -ef | grep FBDA
oracle 18199 8264 0 13:22:12 pts/5 0:00 grep FBDA

--> The background proceess FBDA is running in the background

Check the setting
sqlplus / as sysdba:

select a.ksppinm  "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and ksppinm like '%archiver%'
order by a.ksppinm;


Parameter                            Session Value  Instance Value
----------------------------------- --------------- --------------
_disable_flashback_archiver         0                0
_flashback_archiver_partition_size  0                0

by setting the parameter _disable_flashback_archiver and restarting your instance, you disable the flashback archiver process:

alter system set "_disable_flashback_archiver"=1 scope=spfile;