No DML allowed when flashback data archive quota exceeded

When a flashback data archive exceeds its quota on the tablepsace, it will log an alert on to the alert log but more importantly all DML statements will result in an error until space is added.

1. Create a flashback archive and grant privileges to a user. The flashback archive in this case will have a quota of only 2 MB

sqlplus / as sysdba

SQL> create flashback archive flash1 tablespace asmbkp quota 2m retention 1 year;

SQL> grant flashback archive on flash1 to asanga;

2. Create a table with flashback archiving

conn asanga/***

SQL> create table x ( a char(2000), b char(2000), c char(2000), d char(2000)) flashback archive flash1;

Table created.

3. Insert a single row into the table and continue to update that row

SQL> insert into x values ('x','y','z','i');

SQL> begin
2  for i in  1 .. 100000
3  loop
4  update x set a = i||'x', b = a, c = a, d = a;
5  commit;
6  end loop;
7  end;
8  /
begin
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended
ORA-06512: at line 4

Foreground session receives the flashback runs out of space error and following could be seen on the alert log

ORA-1688: unable to extend table ASANGA.SYS_FBA_HIST_77187 partition HIGH_PART by 1024 in tablespace ASMBKP
Fri Sep 10 02:28:22 2010
ORA-1688: unable to extend table ASANGA.SYS_FBA_HIST_77187 partition HIGH_PART by 1024 in tablespace ASMBKP
Flashback Archive FLASH1 ran out of space in tablespace ASMBKP.
Flashback archive FLASH1 is full, and archiving is suspended.
Please add more space to flashback archive FLASH1.
Flashback Archive FLASH1 ran out of space in tablespace ASMBKP.
ORA-1688: unable to extend table ASANGA.SYS_FBA_HIST_77187 partition HIGH_PART by 1024 in tablespace ASMBKP
Flashback Archive FLASH1 ran out of space in tablespace ASMBKP.

4. Further inserts are also suspended

SQL> insert into x values('1','2','3','4');
insert into x values('1','2','3','4')
          *
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended

Update on 2011-06-29

There has been some new development with regard to the above post. It seem the error thrown is not due to flashback quota exceeding but tablespace quota exceeding. It seem quota limit has no effect.

Below is the test case.

SQL> create tablespace asmbkp datafile '+DATA(datafile)' size 10M autoextend on next 10M maxsize 100M;

SQL>  create flashback archive flash1 tablespace asmbkp quota 2m retention 1 year;

SQL> grant flashback archive on flash1 to asanga;

SQL> conn asanga/asa
Connected.
SQL> create table x ( a char(2000), b char(2000), c char(2000), d char(2000)) flashback archive flash1;

Table created.

SQL> insert into x values ('x','y','z','i');

1 row created.

SQL> commit;

Commit complete.

SQL> begin
 2      for i in  1 .. 100000
 3      loop
 4      update x set a = i||'x', b = a, c = a, d = a;
 5     commit;
 6      end loop;
 7      end;
 8      /
begin
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended
ORA-06512: at line 4

But the error is not due to the fact flashback archive has reached it limit of 2m, ORA-55617 happens because tablespace has reached it max size. This error should have happened when flashback archive internal table reached 2M.

Query showing quota has been set to 2 M

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME    FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
------------------------- ------------------ ------------------------------ -------------
FLASH1                                     1 ASMBKP                         2

Getting the name of the internal flashback archive table

SQL> select * from dBA_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME OWNER_NAME                     FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME   STATUS
---------- ------------------------------ ------------------------- -------------------  -------------
X          ASANGA                         FLASH1                    SYS_FBA_HIST_87387   ENABLED

Size of the internal flashback archive table

SQL> select sum(bytes)/1024/1024 as "MB" from dba_segments where segment_name='SYS_FBA_HIST_87387';

MB
--
96

Maximum and current size of the datafile (only one datafile in this tablespace)

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

TABLESPACE_NAME                   Size MB MaxSize MB
------------------------------ ---------- ----------
ASMBKP                                100        100

Above was tested on 11.2.0.2 With Patchset 11.2.0.2.0 set. Same behavior is also seen on 11.1.0.7 with PSU 11.1.0.7.7. Oracle support has suggested Bug 7120053 - ORA-55617 in flashback archive tablespace even if used size does not reach quota [ID 7120053.8] but issue suggested on the metalink and above issue is not the same.

Update on 2011-07-08
Response from Oracle is The Flashback archiving is handled by fbda Background Process and it checks for Tablespace Quota every 1 Hour. So the Limit can be exceeded until the next fbda-Run detects it. And 11g feature: Flashback Data Archive Guide. [ID 470199.1] will be updated with this information.

So carried out the test but limited the number of loops to 10. This created a flashback archive table of size 8MB exceeding the 2M quote. Waited until fbda to check the quota limit again and following could be seen on the alert log

Fri Jul 08 12:33:49 2011
Flashback Archive FLASH1 ran out of space in tablespace TEST.
Flashback archive FLASH1 is full, and archiving is suspended.
Please add more space to flashback archive FLASH1.

Same message is repeated almost every hour

Fri Jul 08 13:38:49 2011
Flashback Archive FLASH1 ran out of space in tablespace TEST.

And trying to update would cause the following error and no further DML will be allowed.

SQL>  update x set a ='aaa' ,b = a, c = a, d = a;
 update x set a ='aaa' ,b = a, c = a, d = a
        *
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended

SQL> delete from x where a='10x';
delete from x where a='10x'
            *
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended