Re: General question on tablespace creation

"Xavier Maillard" <xma_at_gnu.org> wrote in message news:87hc5a5nl7.fsf_at_gnu.org...
> Hi,
>
> I have a general question about tablespace creation.
>
> Is it better to create a small tablespace auto-extendable to a (defined)
> maxsize or to create it with a "static" size.
>
> Ex:
>
> alter tablespace foo add datafile 'mydbf' size 10240M autoextend
> off;
>
> or
>
> alter tablespace foo add datafile 'mydbf' size 1M autoextend on
> maxsize 10240M;
>
> I tried to find a Note on metalink but failed.
>
> What is your opinion ?
>
> Thank you

It depends. One compromise is to have it autoextend in chunks up to a maximum. For example:
 alter tablespace foo add datafile 'mydbf' size 100M autoextend on next 50M  maxsize 10240M;

If you are going to use autoextend then I would extend in good sized chunks. (perhaps 100M not 50M) I wouldn't create 1 M pieces at a time. The disadvantage of autoextend is if you run out of room in the OS. You could have 10 files on that mount point and if they all tried to autoextend and the combined max was larger than the mount point you have a problem.

On the other hand, if you are using a backup method instead of rman you have to back up a whole static file - even if it has very little data in it.) Jim Received on Thu Dec 11 2008 - 20:50:09 CST