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

"Xavier Maillard" <xma (AT) gnu (DOT) org> wrote

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

"Xavier Maillard" <xma (AT) gnu (DOT) org> wrote

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

"Xavier Maillard" <xma (AT) gnu (DOT) org> wrote

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

"Xavier Maillard" <xma (AT) gnu (DOT) org> wrote

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

"gym dot scuba dot kennedy at gmail" <kennedyii (AT) verizon (DOT) net>
writes:

Thank you very much for these crystal clear explanations. I think
I will use the latter rule: create the tablespace to its maximum
size.

Xavier

"gym dot scuba dot kennedy at gmail" <kennedyii (AT) verizon (DOT) net>
writes:

Thank you very much for these crystal clear explanations. I think
I will use the latter rule: create the tablespace to its maximum
size.

Xavier

"gym dot scuba dot kennedy at gmail" <kennedyii (AT) verizon (DOT) net>
writes:

Thank you very much for these crystal clear explanations. I think
I will use the latter rule: create the tablespace to its maximum
size.

Xavier

"gym dot scuba dot kennedy at gmail" <kennedyii (AT) verizon (DOT) net>
writes:

Thank you very much for these crystal clear explanations. I think
I will use the latter rule: create the tablespace to its maximum
size.

Xavier

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