Re: ORA-01562

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote:
> Looks like you hit the maximum extents allowed (121).

> To fix,

> alter rollback segment RB0 storage (maxextents 2000);

I logged in as sys/password as normal into the DB (Is this correct?)

Did ALTER ROLLBACK SEGMENT RBS1 SHRINK ;

and got 'rollback segment not found'.

Tried RBS1ORCL.ORA and got illegal option.

Then I tried rb0 and rb1 as an argument. Both worked. But I saw no touch on the date of the RBS1ORCL.ORA file. (maybe it wouldn't show in a touching of that file either)

Can I show the rollback segment that is in the game somehow?

I did then blindly the following:

SQLPLUS SYS/password_at_ORCLALIAS

ALTER ROLLBACK SEGMENT RB0 SHRINK ;
ALTER ROLLBACK SEGMENT RB0 STORAGE (maxextents 20000); ALTER ROLLBACK SEGMENT RB1 SHRINK ;
ALTER ROLLBACK SEGMENT RB1 STORAGE (maxextents 20000);

COMMIT; <<<< Is this necessary? Or does it have side effects on pending

              transactions?
QUIT All four statements worked.

I repeated it for the owner of the TABLEs (who had DBA privs, don't know whether it counts).

Then I ran again the table creation job and it worked this time.

Now I wonder which magic had made it to work :-O

Many thanks for helping

--
Chris Christoph P. U. Kukulies kukulies_at_rwth-aachen.de




> Change the 2000 to sdomething more meaningful for your system.




> DO NOT set a rollback segment to 'maxextents unlimited' because Oracle

> will crash oput when you exceed 32767 extents (or therabouts) on a

> rollback segment. I'm not sure which version of Oracle fixes this (if it

> is fixed). There'#s a note on Metalink somewhere about the problem with

> unlimited rollback segments.




> If the rollback segs are in a Locally Managed Tablespace, Oracle limits

> them to 32765 (at 8.1.7.3.0 anyway) so it looks like you shouldn't set

> maxextents higher than 32765.




> Regards,

> Norman.




> -------------------------------------

> Norman Dunbar

> Database/Unix administrator

> Lynx Financial Systems Ltd.

> mailto:Norman.Dunbar_at_LFS.co.uk

> Tel: 0113 289 6265

> Fax: 0113 289 3146

> URL: http://www.Lynx-FS.com

> -------------------------------------





> -----Original Message-----

> From: Christoph Kukulies [mailto:kuku_at_accms33.physik.rwth-aachen.de]

> Posted At: Wednesday, June 19, 2002 11:51 AM

> Posted To: server

> Conversation: ORA-01562

> Subject: Re: ORA-01562





> Telemachus <telemachus_at_ulysseswillreturn.net> wrote:

>> Was there another error message ?




> I asked my colleague: 

> He told me:




> ORA-01562: Fehler beim Erweitern der Rollback-Segmentnummer 2.

> ORA-01628: Max. Anzahl von Extents (121) für Rollback-Segment RB0

> erreicht




> RBS1ORCL.ORA is 12 MB.




> I had set max_rollback_segments in INITORLC.ORA to (astronomical 1000)

> out of sheer desparation.





> Isn't there a way to set something to 'unlimited' to overcome this

> problem?





> --

> Chris Christoph P. U. Kukulies kukulies_at_rwth-aachen.de