Re: max # extents

In article <36361902.2829FE11_at_uno.edu>,   Veronique Molinari <vmolinar_at_uno.edu> wrote:
> Hi,
>
> My application runs on a Personal Oracle 7.3 for Windows 95 database. I
> got an error message indicating that I should expand the rollback_data
> tablespace. After expanding it, I now get the following error message:
> ORA-01562: failed to extend rollback segment number 3
> ORA-01628: max # extents (121) reached for rollback segment RB2
> Can anyone help me solve this problem?
>
> Thanks in advance.
>
> Veronique
>
>

Greetings, Veronique,

I support Oracle on UNIX, but I believe the same principles for the maximum number of extents apply. The maximum number of extents for an object (tablespace, table, index, etc) is a function of the database's block size. So, for a 2K block size, the maximum number of extents is 121; for 4K - 249, for 8K - 505.

You may want to do the following:

1. Shrink your rollback segment back to its original size (if you did not specify OPTIMAL) when you created it:

         ALTER ROLLBACK SEGMENT segment_name SHRINK TO original size;

  2. Change MAXEXTENTS (you may want to make it just shy of the maximum value)

  ALTER ROLLBACK SEGMENT segment_name STORAGE (MAXEXTENTS [the value that pertains to your database's block size]);

It would probably be a good idea to either use the Windows tools to monitor your extents, or to periodically run a SQL script:

    select segment_name, tablespace_name, segment_type, extents     from dba_segments
    where extents > 'some-number-of-extents-you-specify';

Hope this helps.

Yaca Attwood

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Oct 27 1998 - 17:46:19 CST