Re: Rollback

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sat, 03 Dec 2005 16:39:01 +0100
Message-ID: <dmsdjj$i8r$1@news5.zwoll1.ov.home.nl>

Joel Garry wrote:

>>ORA-01628: max # extents (121) reached for rollback segment OBVIOUS3

>
>
> Ouch. Looks like you are using a small (2K) blocksize. You need to
> get in there to look at the code and make modifications to delete just
> a few rows at a time, or else create a large rollback segment and set
> all purge transactions to it. That will get you through the purge,
> then you probably need some administrative work to continue on.
>
> The Oracle Press books Tuning Oracle by Corey and Abbey and the
> Complete Reference might get you going, if they are still in print for
> 8i.
>
> jg
> --
> @home.com is bogus.
> http://www.usatoday.com/news/opinion/editorials/2005-11-29-wikipedia-edit_x.htm
>

Eh?

8i knew maxextents unlimited...

to the OP:
I would create new rollback segments (and a new rollback TS, if needed) with larger blocksize, initial and next, say

CREATE TABLESPACE RBS DATAFILE 'D:\oracle\oradata\demo\rbs01.dbf' SIZE 64M REUSE
AUTOEXTEND ON NEXT 8M maxsize 512M
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 8 MAXEXTENTS 4096); Please note:
- 64MB initial file size, autoextending with 8M will eventually get to 512MB - modify to your situation, as long as the math is correct

128k extents fit in 64MB (file size) - do the math...
every transaction will get 8 segments of 128k each, 1MB in total; do the math: 64 concurrent transactions.
64 concurrent transactions before your file starts expanding; do the math: 8MB expansion means room for 8 more transactions, or all 64 transactions requesting one extra segment.

After that:
CREATE PUBLIC ROLLBACK SEGMENT RBS0 TABLESPACE RBS; (... repeat until enough - depends on your situation, that is: the number of concurrent transactions). CREATE PUBLIC ROLLBACK SEGMENT RBS TABLESPACE RBS; ALTER ROLLBACK SEGMENT RBS0 ON LINE;
... repeat for all segments
ALTER ROLLBACK SEGMENT RBS ON LINE; You could create your rollback segments in your existing table space, but as I do not know the storage clause of it, I cannot tell how many extents to allocate per segment.

If you create a new TS, and rollback segments, you can offline the existing rollback segments, and start dropping them. Drop the old rollback table space as well. Make sure to remove rollback segments from the inti.Nora file - public rollback segments (as used in this example) do not need a reference in inti.Nora. Not taking out the references, after dropping the old rollback segments, will cause the instance to complain about missing rollback segments.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Sat Dec 03 2005 - 09:39:01 CST