Alter or Rebuild a table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Alter or Rebuild a table?

  1. #1
    Join Date
    Mar 2001
    Posts
    52
    Hello,

    I have to reorganize few tables and I am at the point that I donít know if I should rebuild or change storage parameters or leave as they are ??!!

    Based on what I read :
    1. -INITIAL, NEXT parameters should be exactly the same and PCTINCREASE=0
    2. INITIAL, NEXT of the table set at the same value as the INITIAL, NEXT for the tbs.

    My_TBS
    INITIAL =1048576
    NEXT= 2097152
    PCT_INCREASE= 50

    My_table
    INITIAL =10485760
    NEXT =2097152
    PCT_INCREASE= 50


    Any sugestion!
    Thanks.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if it doesnt take much time or you can afford downtime I would reorganize it
    Your configuration in the long go would just cause fragmentation, one of the customer we have is suffering this, we told their *so-called* DBA to set initial=next pctincrease=0 he was insisting he must set a larger initial for each segment to avoid the increase of number of extents now he is suffering it badly, he has tablespace with size of 8GB and only 3 GB is occupied by real data with 5GB of space unable to be reused because of the funny extent sizes he set

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Table's extent size depends upon the growth of the data.
    Assume that a table grows 10 MB every month, then in a year it needs 120MB totally. You can very well set INITIAL and NEXT extent size to 30MB and PCTINCREASE 0. So in a year the table would have only 4 extents. Follow the simple principle always.

  4. #4
    Join Date
    Sep 2001
    Posts
    163
    Pando,
    Why would you not want to set the pctincrease to something other than zero? For example, set it to 1. Oracle will coalesce the free space automatically. Would that not get rid of the 5gb of wasted space or atleast reorganize it so it can be used properly?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    why would I need to coalesce if all my extents are of same size?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width