RESIZE or AUTOEXTEND
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: RESIZE or AUTOEXTEND

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Arrow

    When a datafile in a tablespace is runing out of space, we can either add another datafile to that tablespace or RESIZE / AUTOEXTEND the datafile.

    What is better RESIZE or AUTOEXTEND?

    Personally, I feel AUTOEXTEND is better as RESIZE would increase the size of the file unnecessarily (may be). The impact this has is that backup size of the files are larger.

    Any other issues to be considered?

    Any other views on this?

    Thanks.

    With regards.


  2. #2
    Join Date
    Jun 2001
    Posts
    33
    adding another datafile is the solution ...


  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    If you add another datafile, you can control the growth. I prefer this solution.

    If you enable AUTOEXTEND and your space is limited, you may add space in increments that are larger than desired.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    No one is for AUTOEXTEND/RESIZE?

    I don't want to add another datafile to the tablespace. So, now what are the issues when I use AUTOEXTEND/RESIZE?




  5. #5
    Join Date
    Jul 2000
    Posts
    243
    Hi

    use RESIZE, this way you have control on the space the datafile is takeing. if you have imited storage space on the one hand and the nature of your system is that of a hi variance in space consumption, use RESIZE. if the nature of your space concumption is linear, or you do not have data storeg problems, you can go for the AUTOEXTEND. but still, i'll think that the RESIZE option is better.

  6. #6
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    From what I study of datafiles having AUTOEXTEND, there seems to be very little fragmentation (almost 0) compared to datafiles extended with RESIZE. Is this because of automatic coalescing?

    So, should I conclude that AUTOEXTEND is better?

    [Edited by ggnanaraj on 06-19-2001 at 08:17 AM]

  7. #7
    Join Date
    Jul 2000
    Posts
    243
    Hi

    can you point me to where this is writen? i never herd of that. as far as i know database fragmantetion has nothing to do with the AUTOEXTEND/RESIZE option. it has to do with delete, insert transactions.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    I personally always use the AUTOEXTEND option for my datafiles. Using AUTOEXTEND requires that you know your data. For example, I use different NEXT parameters depending on the size of the extents in the tablespace. I then set the MAXSIZE to the maximum size that my filesystem can handle.

    AUTOEXTEND allows you to reduce the time you are adminstering storage. Yes, you have to watch your storage, but you have to watch it much less. When your datafile aproaches it's MAXSIZE you have to add another datafile on so you don't run out of space.

    I am very hesitent when adding a datafile since they are so hard to get rid of once they are created.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Quote
    -------
    shawish_sababa Hi

    can you point me to where this is writen? i never herd of that. as far as i know database fragmantetion has nothing to do with the AUTOEXTEND/RESIZE option. it has to do with delete, insert transactions.
    ------------
    End Quote

    This is my observation when I query dba_freespace, dba_data_files, dba_tablespaces & dba_segments.


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