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

qazmlp wrote:

> 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!

I think you have been reading the wrong books! Actually, Option 1 is the preferred way to go. There's certainly no performance impact. There might even be performance advantages, if the different files are on different physical hard disks (though that argument is a little moot these days if everything is striped across as many disks as you can shake a stick at).

The real reason these days for breaking tablespaces up into multiple data files is that the data file is the smallest unit of backup and recovery. It is not a particularly good idea to pour all your data eggs into a single disk file basket.

On the other hand, the more data files there are, checkpoints have more work to do to complete. So you don't want to go overboard either. In your case, if you are thinking of resizing a file to become 200M in size, do so. Keep doing so until the file is about 2000M in size -then add a new file, and allow that to grow to 2000M before adding the third and so on.

Some people will have different opinions on the 2000M ceiling I just mentioned (it's been discussed here before). But the generic point is: resize files until they are a 'reasonable' size, but when they reach that point, accommodate further growth of the tablespace by adding in additional files.

Regards
HJR Received on Wed Oct 20 2004 - 16:00:05 CDT