ORA-01628: max # extents (505) reached for rollback segment R05 1

[daph]

Hi everyone,

I got a problem and hopefully, someone can help me out. I got the following error message in SQL Plus:

ERROR at line 1:
ORA-01562: failed to extend rollback segment number 5
ORA-01628: max # extents (505) reached for rollback segment R05

I thought I had figure out how to fix the problem as follows:

ALTER ROLLBACK SEGMENT R05
STORAGE (MAXEXTENTS unlimited);

But THEN, I got this message:

ERROR at line 1:
ORA-01557: rollback segment extents must be at least 4 blocks

Now, I don't understand. Isn't Unlimited the highest value I can put here? If I put 505 instead, it accepts it. Here is the table of my rollbacks. I haven't modified them yet. How can I fix this problem??

SEG_NAME INIT_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------- ----------- ----------- ----------- -----------
SYSTEM 57344 57344 2 505
R05 24576 24576 20 505
R06 24576 24576 20 505
R07 24576 24576 20 505
R08 24576 24576 20 505

Hope someone can help me understand this! Thanks a whole lot.

daph

 

[jimbopalmer]

you can alter each segment offline then drop it then create it with larer extents say 2 meg then alter it online and then go to the next rollback segment. Done one at a time the users do not need to be disturbed, do not mess with system. I tried to remain child-like, all I acheived was childish.

 

[daph]

When you say 2 meg for larger extents...is that the maxextent you are talking about? What confuses me is the intial, next and minimum extent...does that influence what we choose to put as maxextent? If not, I don't see why there we have to re-create it completely.

Could you give me an example of a CREATE ROLLBACK that would be appropriate?

Thanks for your response

daph

 

[jimbopalmer]

no, if you use 8K block size then 505 is a common maximum number of extents in unix.

if you make the initial and next extents 2M instead of 24k, the chance of usung all 505 goes down. I tried to remain child-like, all I acheived was childish.