Rebuild segments

[farrokhp]

I found some segments with more than 100 Meg of free DB Blocks in our PROD DB. Does these segments must be re-build to release space andand speed-up table scans ?
Do I have to export and import them if they are Tables?

[dknight]

Are the segments fragmented? If the data are stored contiguously, a rebuild will provide no performance gains.

It could mean that you have 100mb room for growth.

[farrokhp]

But 100mb room for growth , it means that it's reserved only for this segment right? and nobody else can use it. Is that fine ??

[dknight]

If you have assigned the space to a segment, then it belongs to the segment whether it is filled with data or not.

If it is a 1 GB table growing at 1% per day, then the space will last less than 2 weeks.

If it is a 100 mb table growing at 1% per day, then space will last around 3 months.

In short, it depends on the growth rate and the amount of time you want to spend resizing objects compounded with the overall amount of disk space at your disposal.


[Edited by dknight on 03-17-2002 at 10:55 AM]

[grjohnson]

Mind you, if these tables a lookup tables are NOT expected to grow any larger then this space is effectively wasted. You can DEALLOCATE UNSED to reclaim the space if you so desire.

Although, you can't perform this on LMT's, and it could throw out your segment sizings if you are maintaining manual uniform extents in a Dictionary Managed Tablespace.

Cheers