Re: De-framentation of segments

In article <6euosl$of5$1_at_news3.microserve.net>, Haresh Assumal <assumal_at_sprynet.com> wrote:
>I have a simple question that about database defragmentation of segments in
>Oracle 8.0.3.0 on NT4.0.
>
>I got the following error when trying to insert about 60K rec into a table:
>ORA-01562: failed to extend rollback segment number 4
>ORA-01628: max # extents (121) reached for rollback segment RB2
>
>I increased the number of max number of segments and that go rid of the
>problem. My question is could I have gotten around the error by
>de-fragmenting the segment? If so how do I find out if the data is
>fragmented and how do I de-fragment it?

There are actually several subjects that fall under the heading of "fragmentation", so half the answer is RTFM. And that is a big "half." :)

The other half of the answer is to either have a large rollback segment specifically for doing loads like this, or commit often. Also, you might want to create your rollback segs with a higher maximum number of extents - it defaulted to 121 due to your blocksize (established when you created the database). Check out OPTIMAL also. You may have incorrect INITIAL and NEXT sizing.

>
>Thanks for your help.
>Haresh
>e-mail: assumal_at_sprynet.com
>
>
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!