Re: Adding new datafile question

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0312212136.39e9c69b_at_posting.google.com>...
> ITAPORT06 <mokat67_at_hotmail.com> wrote :
>
> > Orcale 8i on WINNT
> >
> >
> > I have a tablespace with one datafile (1) which has almost grown to
> > 2080MB. I read on the forum that it is better not going over 200OMB
> > so I will add a 2nd datafile to this tablespace.
>
> Huh? Who was the ass who said that?
>
> There is, or was, a 2GB file limit on Unix. Maybe still is on some
> Unix flavours.
>
> However, from an Oracle perspective is does not matter whether a
> datafile is 1GB or 3GB. If smaller files are better, then Oracle would
> have suggested a recommended practise of only creating small
> datafiles. Oracle does not.
>
> > Datafile (1) has the following extend option: " autoextend on next
> > 5120K"
>
> I think auto extending datafiles are an evil. It removes control from
> *you* and gives it to a piece of software to do. I can never be
> comfortable not controlling how much space has been allocated to the
> database. Worse, auto extends make a damn poor crutch for space
> planning.

Billy,

There were some bugs with filesizes of exactly 2 GB throwing errors. I don't recall exactly what versions (patchsets) they were fixed.

Even Oracle 7.3.4 allowed a datafile size to be 32 GB, that is not the issue.

I'm just still superstitious of 2 GB datafiles. I also used a command line comress utility that used to puke on files larger than 2 GB. A 8 GB tempfile?, sure. I never back those up.

Now, should the OP have a 5 MB autoextend "NEXT" size? NO.
A 128 MB autoextend size?
YES. I don't see extents in the (OS) filesystem of 128 MB being thrown as a terrible practice. I believe that in the Linux 2.4 Kernel with ext3, that 128 MB is the largest section of contiguous space that can be allocated.

Yes, it would be far better to plan/monitor/allocate sufficient space manually, but with where 10g is headed, I guess that we can start getting used to allowing oracle to manage such things more. But I still set maxsize on autoextend and get daily storage utilization reports emailed to me daily.

He may want to consider creating another tablespace that has larger extents. (check out the "optimal storage configuration made easy" paper - "how to stop defragmenting and start living")

If the database is not large, it doesn't hurt to just keep datafile sizes to say 1 GB and add datafiles to tablespaces as appropriate. But then again, I have to worry about (compressed) backup sets being sent off-site over a vpn for disaster recovery purposes, and would much rather re-send a compressed 1 GB file, rather than a compressed 4 GB file.

Pd Received on Mon Dec 22 2003 - 18:48:23 CST