I know how to fix the rollback segment size issue, you say, recalling how the error could be circumvented in the olden days. Just issue a "set transaction use rollback segment real_big" statement. In 10g, here is what the statement and result looks like:

Problem fixed, right? Well, no, not really. If you are using 10g in AUM mode, try either the command shown above or even this one: set transaction use rollback segment make_believe.

Why you can still receive ORA-01555 errors

The reason you can still experience this error is that your undo retention parameter value is not high enough. One solution is to set a higher value. However, having read something about new features in 10g, something that may stand out is how 10g can automatically tune the retention period. Are you supposed to set a retention period value or not? Shown below is Oracle's recommendation (Database Concepts):

Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:

• For an AUTOEXTEND undo tablespace, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows. In addition, when there is adequate free space, the tuned retention period does not go below the value of the UNDO_RETENTION initialization parameter.

• For a fixed size undo tablespace, the database tunes for the maximum possible undo retention. This means always providing the longest possible retention period while avoiding out-of-space conditions and near out-of-space conditions in the undo tablespace. The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.

What are the rules – to set, or not to set - regarding the setting of UNDO_RETENTION? You must set the undo retention parameter when:

• The undo tablespace has the AUTOEXTEND option enabled
• You want to set undo retention for LOBs
• You want retention guarantee

Retention Guarantee

Retention guarantee is a feature found in Oracle 10g Release 2. If you use the "RETENTION GUARANTEE" clause in a "create undo tablespace" statement (either during database creation or afterwards), or use it within an "alter tablespace" statement, you are telling Oracle to guarantee that whatever retention period you have set will be honored. This is especially useful for flashback operations, but the potential risk is that future DML statements may fail because their generation of undo data will cut into the space needed by Oracle to guarantee your unexpired data stays that way.

Related to the setting of this value is the size you set (or not) for your undo tablespace(s). Enabling automatic extension (AUTOEXTEND ON) allows Oracle to allocate enough disk space to guarantee the specified retention period. You do run the risk, however, of a "runaway query" consuming significant amounts of space.

How do you know if your undo tablespace has guaranteed retention set?

SQL> select tablespace_name, retention
  2  from dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ ----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

Seeing "NOT APPLY" is an excellent sign that a tablespace is not an undo tablespace because only "GUARANTEE" and "NOGUARANTEE" are applicable to undo tablespaces. As a tip, in the event you are taking over a new database and have to find out which tablespaces are undo or not (although there are other more direct ways, such as querying tablespace names in DBA_UNDO_EXTENTS), the query above will come in handy.

What about setting a rollback segment for a transaction?

If you set a transaction to use a rollback segment named "make_believe," how could that have worked if you never even created or named a segment like that in the first place? That is a bug or "feature" in Release 2. The only "real" rollback segments are related to the data dictionary (and you have no direct control over them anyway), and if in AUM mode, rollback segments are about as useful as a screen door on a submarine. If (and why?) you are still using rollback segments in 10g and want to convert to AUM, a manually run PL/SQL block based off of the undo advisor can be used to help set an initial size or value of AUM settings.

set serveroutput on
DECLARE
   utbsiz_in_MB NUMBER;
BEGIN
   utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
end;
/

In Closing

Automatic Undo Management by itself is not new, but there are several significant enhancements (i.e., differences) between 10g Release 1 and Release 2. I have seen the look of surprise on someone's face (myself included) when the ORA-01555 error appears when using 10g, and the first words or reaction is, "How is that even possible when I'm not using rollback segments?" The truth of the matter is that it can and does happen, perhaps with regularity in some environments. Understanding how (and when) certain AUM settings affect recovery is important because not having the correct settings or values may lull you into a false sense of security. You may think you can flashback 24 hours, and then be in for a rude surprise when all you really have is 15 minutes.

Aside from several (and quite good) recently released books on Oracle 10g, to include Expert Oracle Database 10g Administration by Sam Alapati, articles at OTN are always a good reference or starting point to learn more about AUM.

Back to DBAsupport.com