ORA-01555: snapshot too old, When Running Flashback Query

Argh! Of all the frustrating, partially-completed features Oracle has released, this is the most frustrating. Did I mention this frustrating feature is frustrating when you get bitten by it? Why? Because you only need it when you actually really, really need it.

What am I referring to? FLASHBACK QUERY on a table that lives in a database with a large UNDO_RETENTION specified with lots and lots of UNDO_TABLESPACE space.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     604800
undo_tablespace                      string      UNDOTBS_1
SQL>

So, what do we have here? UNDO_RETENTION to set somewhere in the 7-days range. How much space do we have in the tablespace?

SQL> col bytes format 9999999999999999
SQL> col file_name format a40 
SQL> set lines 180

SQL> 
SQL> select tablespace_name, file_name, bytes, autoextensible, 
maxbytes from dba_data_files 
where tablespace_name like 'UNDO%' 
order by 1,file_id;

TABLESPACE_NAME                FILE_NAME                                            BYTES AUT   MAXBYTES
------------------------------ ---------------------------------------- ----------------- --- ----------
UNDOTBS_1                      /oradata/GVPRD/undotbs_1_02.dbf                 2097152000 YES 8589934592
UNDOTBS_1                      /oradata/GVPRD/undotbs_1_03.dbf                 2097152000 YES 8589934592
UNDOTBS_1                      /oradata/GVPRD/undotbs_1_01.dbf                 2097152000 YES 8589934592
UNDOTBS_2                      /oradata/GVPRD/undotbs_2_01.dbf                 2097152000 YES 8589934592
UNDOTBS_2                      /oradata/GVPRD/undotbs_2_02.dbf                 2097152000 YES 8589934592
UNDOTBS_2                      /oradata/GVPRD/undotbs_2_03.dbf                 2097152000 YES 8589934592
UNDOTBS_3                      /oradata/GVPRD/undotbs_3_01.dbf                 2097152000 YES 8589934592
UNDOTBS_3                      /oradata/GVPRD/undotbs_3_02.dbf                 2097152000 YES 8589934592
UNDOTBS_4                      /oradata/GVPRD/undotbs_4_01.dbf                 2097152000 YES 8589934592
UNDOTBS_4                      /oradata/GVPRD/undotbs_4_02.dbf                 2097152000 YES 8589934592

10 rows selected.

This is enough UNDO based on the number of transactions on this system. Theoretically, I would expect my undo to expand to accommodate the retention period I have set for FLASHBACK QUERY purposes.

Now, let’s see what I get when I try to use it:

SQL> select * from ivr.call as of timestamp (sysdate-3) 
where vpsid = hextoraw('50246A7FF0026946872AC67112A232EF');
select * from ivr.call as of timestamp (sysdate-3) 
where vpsid = hextoraw('50246A7FF0026946872AC67112A232EF')
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 195 with NAME
"_SYSSMU195$" too small

What?! And, it looks like Oracle hasn’t tried to AUTOEXTEND the files at all. They are exactly the same size as before. Grrrr! So . . .  I auto-extend all the files manually with an alter database datafile [file_name] resize 6g;

Edit: We’ve had some unrelated activity since this post was originally written with long-running queries that grew the files further, the point is, I re-sized them up to ~6GB each from ~2GB each.

SQL> select tablespace_name, file_name, bytes, autoextensible, 
maxbytes from dba_data_files 
where tablespace_name like 'UNDO%' 
order by 1,file_id;

TABLESPACE_NAME                FILE_NAME                                            BYTES AUT   MAXBYTES
------------------------------ ---------------------------------------- ----------------- --- ----------
UNDOTBS_1                      /oradata/GVPRD/undotbs_1_02.dbf                 7918845952 YES 8589934592
UNDOTBS_1                      /oradata/GVPRD/undotbs_1_03.dbf                 7516192768 YES 8589934592
UNDOTBS_1                      /oradata/GVPRD/undotbs_1_01.dbf                 7918845952 YES 8589934592
UNDOTBS_2                      /oradata/GVPRD/undotbs_2_01.dbf                 7381975040 YES 8589934592
UNDOTBS_2                      /oradata/GVPRD/undotbs_2_02.dbf                 7650410496 YES 8589934592
UNDOTBS_2                      /oradata/GVPRD/undotbs_2_03.dbf                 7918845952 YES 8589934592
UNDOTBS_3                      /oradata/GVPRD/undotbs_3_01.dbf                 6442450944 YES 8589934592
UNDOTBS_3                      /oradata/GVPRD/undotbs_3_02.dbf                 6442450944 YES 8589934592
UNDOTBS_4                      /oradata/GVPRD/undotbs_4_01.dbf                 7650410496 YES 8589934592
UNDOTBS_4                      /oradata/GVPRD/undotbs_4_02.dbf                 7784628224 YES 8589934592

10 rows selected.

SQL> select * from ivr.call as of timestamp (sysdate-3.5) where vpsid = hextoraw('50246A7FF0026946872AC67112A232EF');

SQL> select count(*) from ivr.call as of timestamp (sysdate-5) where vpsid = hextoraw('50246A7FF0026946872AC67112A232EF');

  COUNT(*)
----------
       287

SQL>

Nice. Why is this behaving this way? Silly me, I really should read Oracle docs more carefully.

Here’s what they say at Managing Undo for Your Database

Undo Tablespace Size and Retention Time

[ . . . ]

To control the retention of undo records, Oracle maintains an undo retention period. This period indicates the amount of time that must pass before Oracle overwrites undo data. The undo retention period affects the size of the undo tablespace; the longer the retention period, the more space is needed.

The undo retention period should be at least as long as your longest-running query. By default, Oracle automatically extends the undo tablespace to accommodate the longest-running query based on your ongoing system activity. Nevertheless, you might need to manually increase the size of your undo tablespace in the following circumstances:

* When your undo tablespace is set to a fixed size (auto-extend disabled) and long running queries are failing with snapshot too old errors.

* When you plan to use Flashback features to recover from user errors such as unintentional changes. In this case, the undo retention should be set equal to the period between the present and the earliest point in time to which you want to return. For more details on Flashback features, see Oracle Database Administrator’s Guide.

There you have it folks. Please don’t get bitten with this the way I did.

Cheers,
Shakir!

P.S.: Love your broccoli!