FlashBack Archive run out of space

FlashBack Archive run out of space. 

Whenever you execute any DML statement you will encounter ORA-55617 Error which means Flashback Archive runs out of space and tracking on table is suspended. 

Following is the sample of DML command and the error message.

SQL> update employees set salary=salary*1.1;
update employees set salary=salary*1.1
       *
ERROR at line 1:
ORA-55617: Flashback Archive "FLA1" runs out of space and tracking on
"EMPLOYEES" is suspended

The is because flashback archive has reached its limit, ORA-55617 happens because either flashback archive quota has reached or tablespace has reached it max size. Query the name of the tablespace used for flashback archive and its quota.

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME         QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FDA1          1 FDA_TBS1         100
FLA1          2 FLA_TBS1         10

SQL> 

Now it is clear that Flashback archive FLA1 has 10MB quota on tablespace FLA_TBS1. Verify the size of tablespace and resize the datafile if required before heading ahead. Find the size of the tablespace

SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" from dba_data_files where tablespace_name='FLA_TBS1';

TABLESPACE_NAME     Size MB MaxSize MB
------------------------------ ---------- ----------
FLA_TBS1         10     0

Find the datafile of tablespace FLA_TBS1.

SQL> select t.name,d.name
  2  from v$tablespace t join v$datafile d
  3  using (ts#)
  4  where t.name='FLA_TBS1';

NAME
------------------------------
NAME
--------------------------------------------------------------------------------
FLA_TBS1
/home/oracle/BACKUP/fla_tbs01.dbf

Resize datafile with alter database command

SQL> alter database datafile '/home/oracle/BACKUP/fla_tbs01.dbf' resize 1000m;

Database altered.

SQL> 

Verify the size of the tablespace now.

SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" from dba_data_files where tablespace_name='FLA_TBS1';

TABLESPACE_NAME     Size MB MaxSize MB
------------------------------ ---------- ----------
FLA_TBS1        1000    0

SQL> 

Increase the quota of the flashback archive fla1 to 50M

SQL> alter flashback archive fla1
  2  modify tablespace FLA_TBS1 quota 50m;

Flashback archive altered.

Now any DML command will execute successfully till the quota exceed.

SQL> update employees
  2  set salary=salary*1.1;

107 rows updated.

SQL>