RESIZE or AUTOEXTEND

[ggnanaraj]

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.

[armel]

adding another datafile is the solution ...

[dknight]

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.

[ggnanaraj]

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?

[shawish_sababa]

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.

[ggnanaraj]

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]

[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.

[marist89]

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.

[ggnanaraj]

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.