Re: 'Adding new data file' vs 'extending existing data file size' to increase Tablespace

"qazmlp" <qazmlp1209_at_rediffmail.com> wrote in message news:db9bbf31.0410200930.2fa3a642_at_posting.google.com...
> As the Tablespace is almost filled on the running system now, we have
> to add additional space to the Tablespace.
>
> As I understand, the following are the two options available to do
> that:
> 1)
> Add a new data file to hold the additional data in Tablespace:
> SQL> alter tablespace my_TS add datafile '/someDir/my_TS2.dbf' size
> 100M;
>
> 2)
> Resize the existing Tablespace's data file.
> SQL> alter database datafile '/someDir/my_TS1.dbf' resize 200M;
>
> What is the best way to go?
> In my understanding, the 1st option is not preferable as the
> Tablespace is spread over 2 data files and hence, performance may get
> impacted slightly.
> Hence, I would prefer to go for the 2nd option.
>
> Kindly give your opinion on it!

Why do you think performance would degrade when using 2 datafiles ? Using multiple datafiles can allow you to increase I/O performance considerably if the filesystems are mounted on different disks. Also, smaller datafiles take less time to restore from backup (well in this case the difference between 100 and 200 MB is nihil I suppose).

Hth,
Matthias Received on Wed Oct 20 2004 - 15:20:32 CDT