Resolving The Problem

If you are running Oracle 9i or higher, you should be using an undo tablespace with automated undo management instead of a rollback tablespace. The use of an undo tablespace will prevent most of these errors.

ORA-01547 failed to allocate extent of size nnn in tablespace xxx

There is not enough room in the tablespaces file(s) to add an extent of nnnblocks.

Increase the size of the rollback tablespace by one of the following two methods. Both can be done
while users are active.

1. Add a datafile.

(a) Connect to ORACLE as SYSTEM via SQLPLUS and execute the following to see
the list of tablespaces and the files they own:

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME,FILE_NAME;

(b) Add a datafile to the rollback tablespace (RBS on UNIX, ROLLBACK_DATA on NT or NLM).
Use the same path as the rollback tablespace files above, but a distinct filename. Use
a size of nnn megabytes for the new file.

ALTER TABLESPACE rollback_tablespace_name ADD DATAFILE
'full_path_and_filename' SIZE nnnM;


2. Turn on AUTOEXTEND for one of the rollback datafiles (ORACLE 7.2.x or later only)

Connect to ORACLE as SYSTEM via SQLPLUS and execute the following to see
the list of tablespaces and the files they own:

SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME,FILE_NAME;

Turn on AUTOEXTEND and limit maximum size to nnn megabytes.

ALTER DATABASE DATAFILE 'existing_rollback_file_path_and_name'
AUTOEXTEND ON MAXSIZE nnnM;



ORA-01555 snapshot too old (rollback segment too small)

This generally occurs when a long running query shares a rollback segment with an
active UPDATE, INSERT, or DELETE. Instead of adding a new extent to the rollback
segment, ORACLE overwrites one of the extents used by the SELECT in the belief that
it has not been referenced recently enough to still be needed.

The usual fix for this is to recreate your rollback segments with more and/or larger
initial extents. You may also need to increase the size of the rollback tablespace
as per the ORA-01547 error above.

To recreate rollback segments, execute the following statements for each rollback
segment. The value for minextents should be large enough so that minextents times
initial is larger than the size of the table for which the SELECT error occurred.

Connect to ORACLE as SYSTEM via SQLPLUS and execute the following:

ALTER ROLLBACK SEGMENT name OFFLINE;

DROP ROLLBACK SEGMENT name;

CREATE ROLLBACK SEGMENT name TABLESPACE ts_name
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS nn );

ALTER ROLLBACK SEGMENT name ONLINE;

If you have active users, you will find that in-use rollback segments will not go offline
immediately, but will go into a PENDING-OFFLINE state. You must then wait until
the segment goes offline to DROP and CREATE it. You can check on the status
by typing:

SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;



ORA-01556 maximum number of extents exceeded

The database attempted to allocate more than MAXEXTENTS to the rollback segment.
The default value of MAXEXTENTS is dependent on your operating system's disk blocksize.
Usually 121 or 249 on unix and 121 on NT/Novell.

You should recreate your rollback segments with a larger INITIAL and NEXT value.

To check the current values, connect to ORACLE as SYSTEM via SQLPLUS
and execute the following:

SELECT SEGMENT_NAME,INITIAL_EXTENT,NEXT_EXTENT FROM
DBA_ROLLBACK_SEGS;

The sizes will be in bytes. Execute the ALTER/DROP/CREATE/ALTER sequence
as for the ORA-01555 error above to recreate the rollback segments with larger extents.

If you are running version 7.3 or higher, you can also increase the MAXEXTENTS value:

ALTER ROLLBACK SEGMENT name DEFAULT STORAGE
(MAXEXTENTS larger_value);

The above command must be repeated for each rollback segment.


ORA-01562 failed to extend rollback segment

This is normally followed by another error. Follow the procedure for the subsequent
error message. You may need to examine the database alert log to determine
the subsequent error.

The location of the alert log can be determined by running the following statement
from the ORACLE SYSTEM login:

SELECT VALUE FROM V$PARAMETER WHERE NAME
= 'background_dump_dest';


ORA-01552 cannot use system rollback segment for non-system tablespace ...

Your public rollback segments are offline, or perhaps were never defined.

Check the status of your rollback segments by connecting to ORACLE as SYSTEM and
typing:

SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

You will likely see that all except SYSTEM are OFFLINE. Put them online with the command:

ALTER ROLLBACK SEGMENT name ONLINE;

There is a good chance that you do not have a rollback_segments parameter in your
initxxx.ora file, or it has been commented out. You should have something like:

rollback_segments = (name1,name2,...,namen)

Listing each of your non-system rollback segments. This will cause them to be brought
online automatically at each startup (instead of having to use ALTER commands).

If the SELECT statement above shows NO rollback segments other than system, you will
need to create them (see commands for error ORA-01555 above) and also add the
rollback_segments parameter to your initxxx.ora file.