Segment Advisor Recommendation - Shrink

[TheDash]

Hi members,
In 10g database the tablespaces were created as assm (auto segment space management). The segment advisor is now recommending that some segments be "shrinked". What are the pros and cons of shrinking? Will there be an overhead should it require more space all of a sudden? Thank you.

 

[SantaMufasa]

Dash,

The first things that I would look at are the causes of the segments to become candidates for shrinkage, then address those causes. Does Oracle "Segment Advisor" explain why it thinks your segments need shrinking?...or what led to the need in the first place?

My method for eliminating "Swiss Cheese" from fragmented tables is:

alter table <table_name> move parallel;

...which reorganizes a table into fresh extents with the table's default freespace. The statement is extremely fast (especially in "parallel"), so there should be little concern for performance hits.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”