Search code examples
oracle-databaseoracle11goracle-pro-c

ORA-01562: failed to extend rollback segment number

I am reading a file row by row and I am committing the data to DB after every row. The input file contains 100K rows. Once in a while I am getting ORA-01562: failed to extend rollback segment number error and it is not for a particular row, nor for a particular file. The error is random. This is happening on production system.

And the MAXEXTENTS for that table is already set to UNLIMITED. I am unable to reproduce the error on my development environment.

How to handle this scenario? What other steps I need to take so that I wont get this error in the future?

Solution

  • What you need to do is to shrink the rollback segment or add another rollback segment.

    In your case it looks like you have only one rollback segment, the default SYSTEM rollback segment.

    You must add atleast one more rollback segment.

    Here are the commands which will help you

    issue these statements while logged in as SYSTEM user.

    Shrinking a Rollback Segment Manually:

    To shrink a rollback segment using you must have the ALTER ROLLBACK SEGMENT system privilege.

    You can manually decrease the size of a rollback segment using the SQL command ALTER ROLLBACK SEGMENT. The rollback segment you are trying shrink must be online.

    The following statement shrinks rollback segment RBS1 to 100K: 

    ALTER ROLLBACK SEGMENT rbs1 SHRINK TO 100K;

    Displaying Rollback Segment Information:

    The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. For example, the following query lists the name, associated tablespace, and status of each rollback segment in a database: 

    SELECT segment_name, tablespace_name, status
         FROM sys.dba_rollback_segs;

    Output

    SEGMENT_NAME  TABLESPACE_NAME     STATUS
 ------------- ----------------    ------
SYSTEM        SYSTEM             ONLINE
 PUBLIC_RS     SYSTEM             ONLINE
 USERS_RS      USERS              ONLINE

    Creating Rollback Segments:

    To create rollback segments, you must have the CREATE ROLLBACK SEGMENT system privilege. To create additional rollback segments for a database, use either the Create Rollback Segment property sheet of Enterprise Manager, or the SQL command CREATE ROLLBACK SEGMENT. The tablespace to contain the new rollback segment must be online.

    The following statement creates a public rollback segment named USERS_RS in the USERS tablespace, using the default storage parameters of the USERS tablespace: 

    CREATE PUBLIC ROLLBACK SEGMENT users_rs TABLESPACE users;