Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Rollback segments

Re: Rollback segments

From: Howard J. Rogers <howardjr_at_www.com>
Date: Thu, 7 Jun 2001 18:44:53 +1000
Message-ID: <3b1f3f29@news.iprimus.com.au>

"Andrew Mobbs" <andrewm_at_chiark.greenend.org.uk> wrote in message news:iYn*Sv6Xo_at_news.chiark.greenend.org.uk...
> [superseded to fix typo and add info]
> Peter Shvets <pshvets_at_powermarket.com> wrote:
> >Hello, folks.
> >I see the following error when load some tables in my warehouse:
> > ORA-01555: snapshot too old: rollback segment number 1 with name
 "RBS0"
> >too small
> >
> >I am not a DBA, so I don't know how to deal with such problems.
> >Could anybody help me or give a hint, please.
> >I appreciate any help.
>
> When Oracle performs a transaction, it keeps a log of what it needs to do
> to undo that transaction. These are stored in "rollback segments". As the
> error says, the problem is that Oracle cannot finish the transaction to
> load the data because there isn't enough space in your rollback segments
> to store the undo information.
>
> This may because the tablespace the rollback segment is in is full, or
> it may be that the rollback segment has hit a defined size limit.
>

Sorry, Andrew. But that is not what is happening. If the rollback segment cannot extend, that will produce a 'cannot extend' error, not a 1555. Snapshot too old means that you have wrapped around the rollback segment, and overwritten a block of rollback data which, although no longer required for transaction rollback, is still required for read consistency purposes.

> You can either commit more frequently, so each transaction is smaller,
> or you can modify the rollback segments to allow the larger transaction.
>

Committing more frequently won't help. It might actually make things worse. An uncommitted rollback block cannot be overwritten, and therefore will always be available for read consistency purposes. The real cure is to make the segment larger so that it takes longer (preferably MUCH longer) to wrap around the segment and start over-writing old blocks.

> You can query DBA_ROLLBACK_SEGS to find details of the rollback
> segments. You should either use ALTER ROLLBACK SEGMENT to modify the
> rollback segment or ALTER TABLESPACE to modify the tablespace.

Altering the tablespace won't achieve a thing (the segment has already been created and therefore has its own storage clause). Altering the rollback segment won't make it any bigger (altering minextents, for example, won't cause the segment to acquire extra extents). The only cure is to drop the rollback segment, and re-create it with bigger extents.

Regards
HJR
>You
> can find documentation for these commands in the Oracle SQL Reference
> manual, you can find the DBA_ROLLBACK_SEGS view in the Oracle Reference
> manual, you can find more information about rollback segments in
> the Oracle Concepts manual. The documentation is online at:
> http://otn.oracle.com/
>
>
> --
> Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Received on Thu Jun 07 2001 - 03:44:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US