Re: General question on tablespace creation

Xavier Maillard wrote:
> 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

You didn't tell us which version you are working with... and in this case the answer can vary.

If you know you are going to need 1G - the allocate it up front. File extensions will cause a slowdown at that point when it needs to extend. And if it has to extend 50x a day, that is quite a bit of wasted processing...

Depending on your company resources, using ASM with RAW devices and presenting it with the necessary disk space, you won't have to create another datafile as it will be done automagically for you.

In many of our environments - our dbas have not had to get paged to handle an out-of-space issue since moving to ASM. You may not have that luxury as few environments get to have an almost unlimited amount of "disk space". Even for smaller environments, I would still recommend it just so you get to sleep at night :)

Barring that, I have in the past had cron and/or dbms jobs that would periodically check the space on my tablespaces (pre-10g/ASM) and notify long before it became a problem. One of the issues was to determine whether or not a 95% full tablespace was going to be a problem. Example: tablespaceA at 95% full meant I had 100MB free - and only grew by 1-2MB/mth. tablespaceB at 95% full meant I had 1.5GB free and it grew at a rate of 100MB/week. So the thresholds were different for each tablespace being monitored. Received on Sun Dec 14 2008 - 15:56:48 CST