Re: Online creation/extension of Tablespaces possible??

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 12 Sep 2004 06:44:47 +1000
Message-ID: <414363c5$0$9653$afc38c87@news.optusnet.com.au>

Sybrand Bakker wrote:

> Comments embedded
>
> On 11 Sep 2004 02:13:10 -0700, qazmlp1209_at_rediffmail.com (qazmlp)
> wrote:
>

>>We have a requirement for increasing the size of the existing
>>Tablespaces. In my understanding, this could be done in either of the
>>following ways:
>>  1.adding one more datafile to the tablespace

>
> Not preferred unless you hit the max datafile size for your O/S

Actually, I disagree with that. Assuming that your existing data files are of "sensible" size (a couple of Gigs), then I would prefer someone to add additional datafiles to their tablespace to make it bigger than to allow their existing files to get bigger.

The reason is simply one of backup/recovery issues and the law of probabilities: the more eggs one keeps in bigger baskets, the more mess there is when the basket fails.

Likewise, the data file is the smallest unit of user-managed backup and recovery, so that's a lot of backing up and a lot of restoring to do to recover a database.

Too many data files in a database are themselves not a terribly good idea for efficient checkpoints etc, so I wouldn't want to see 2000 files of 10MB each to make a 20GB database, either. But given the assumption I mentioned earlier, that your datafiles are at least 1 or 2GB in size, I would prefer to see 10 2GB files than (say) 4 5GB files.

I think we've discussed these issues in the past, so it's fine if we don't agree. But the OP shouldn't go away thinking that there are no dissenting voices on the matter!

>>  2.resize the datafile to a high value
>>  3.autoextend ON for system datafile

>
>
> autoextend can be set on for the datafiles in the *affected
> tablespaces*, not necessarily the system tablespace.

True, but it's a terrible way to manage tablespace size issues for any tablespace other than SYSTEM.

Out of that list of three options, number 2 is my preferred method. Though 10g's ASM is making me think twice about this sort of hands-on management issue.

Regards
HJR Received on Sat Sep 11 2004 - 15:44:47 CDT