Resize the Storage parameter for Index creation
Can u please guide me if it is possible for me to resize the storage parameter in creating an index.Previously i had created an index using higher storage parameters say Initial Extents and Next Extent sizes.Now i want to alter these parameters ie make it lower than what i have created it.What are the implications by doing so?Is it possible in the first case.I'm trying to change it in UAT-Environment.I created the index with a very huge initial extent size now i want to reduce it by 50%.
And one more clarification can the next extent size be 10% of Initial Size.Your suggestions would greatly help me.Please do advice.I'm working on Oracle 9i Rel.2. on HP-Unix.
Have a look at ALTER INDEX REBUILD in the SQL manual - you can redefine the storage parameters.
Current thinking is to have PCTINCREASE=0, INITAL=NEXT= same value for all objects in a tablespace (preferably LMT). Yes, it's OK to have 100's of extents. See: http://otn.oracle.com/deploy/availab...pdf/defrag.pdf
You cannot decrease the initial extent, you cannot deallocate it - so you must rebuild the index (you can do this online without locking the table).
Click Here to Expand Forum to Full Width