ORA-1650: unable to extend rollback segment RBS1 by 128 in tablespace RBS

 Failure to extend rollback segment 2 because of 1650 condition
ORA-1650: unable to extend rollback segment RBS1 by 128 in         tablespace RBS

Failure to extend rollback segment 2 because of 1650 condition
Failure to extend rollback segment 2

 

 

Bookmark
Fixed font
Go to End


Doc ID:
Note:1066542.6
Subject:         UNABLE TO EXTEND ROLLBACK SEGMENT
Type:         PROBLEM
Status:         PUBLISHED
        Content Type:         TEXT/PLAIN
Creation Date:         02-MAR-1999
Last Revision Date:         12-JUN-2004

Problem Description:
====================

You are working with the database and you encounter ora-1562.

Error:        ORA 01562
Text:        "failed to extend rollback segment number %s"
------------------------------------------------------------------------  
Cause:         Failure occurred when trying to extend rollback segment
Action:         This is normally followed by another error message that caused  
                     the failure. You may take the rollback segment offline to  
                      perform maintenance.  Use the alter rollback segment offline  
                      command to take the rollback segment offline.

The 0RA-1562 is often followed by another error which indicates the cause of the failure.  
For example, ORA-1650.

Error:          ORA 1650  
Text:           unable to extend rollback segment <name> by <num> in tablespace <name>
-------------------------------------------------------------------------------
Cause:          Failed to allocate extent for the rollback segment in tablespace.
Action:         Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more  
                files to the specified tablespace.

You may try using "set transaction use rollback segment" command to force the use of a large  
rollback segment for a large transactions, but you still receive the ORA-1562.
User transactions are recorded in the rollback segment. The transaction remains open in  
the rollback segment until the user commits the changes.  If the user does not commit the  
changes, other users' transactions will eventually wrap around the rollback segment  
and "bump into" the open transaction.  At this point any new transactions will  
attempt to grab a new extent for the rollback segment.  


Solution Description:
=====================

Obtain the information necessary to address this issue.

For the ORA-1650:

select max(bytes)
from dba_free_space
where tablespace_name = ?<tablespace name>?;

The above query returns the largest available contiguous chunk of space.

Select next_extent, pct_increase,
from dba_rollback_segs
where segment_name = ?<rollback segment name>?;

The pct_increase is 0 by default.

There are several options for solving the failure to extend a rollback segment.

Ask users to commit their transactions to free space in the rollback segment  

Manually coalesce adjacent free extents.
     Alter tablespace <tablespace name> coalesce;
     The extents must be adjacent for this to work.         

Add a datafile.
     Alter tablespace <tablespace name> add datafile ?full path and file name?
     size <integer> <K|M>;

Resize the datafile.
     Alter database datafile <full path and file name> resize <integer> <K|M>;

If you tried using "set transaction use rollback segment" and received  
and unable to extend error, as a workaround take all other rollback  
segments offline. SET TRANSACTION USE ROLLBACK SEGMENT won't force DDL  
to use a particular rollback segment.  It's only effective on DML. All DDL  
begins with an implicit COMMIT.  If you issue the SET TRANSACTION  
command, followed directly by DDL, the DDL will end the previous  
transaction as its first action.  You cannot direct Oracle to use a specific  
rollback segment for DDL, only for DML.


Solution Explanation:
=====================

Once the largest contiguous space and the next extent size are known it can  
be shown why the failure occurred. The failure to extend error is raised  
when the next extent size is greater than the largest contiguous space.  

This error is only generated if, at the time the object tried to extend,
there was not enough available contiguous space.


Solution References:
====================

Note 1025288.6
.


Copyright © 2005, Oracle. All rights reserved. Legal Notices and Terms of Use.

 

 

from：http://www.itpub.net/thread-390367-1-1.html