Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: max datafile size

Re: max datafile size

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sun, 4 Nov 2001 09:22:12 +1100
Message-ID: <3be46df9$0$5045$afc38c87@news.optusnet.com.au>


Comments below
HJR

-- 

Oracle Resources : http://www.hjrdba.com
=============================


"Frank Hubeny" <fhubeny_at_ntsource.com> wrote in message news:3BE450DF.1000008_at_ntsource.com...

> I can think of two reasons to allow files to go beyond 2 gigabytes:
>
> (1) Management: There are fewer files to maintain in larger tablespaces.
>
I'd rather manage 50 convenient files than 5 humungous buggers that are a pain in the neck to back up and restore. Big files can be far more of a management nightmare than small ones.
> (2) Design: Tablespaces can be more easily designed with one datafile each.
>
How much design activity exactly is there in creating a tablespace?? Sounds pretty weak as a justification to me.
> The fact that there is no 2-gigabyte limit for most systems that would
> like to go beyond this really places the burden on those who want to
> keep this obsolete restriction to justify it. I have not yet heard a
> good justification.
>
> The issue raised about backing-up a large file is no different from the
> issue of backing up a tablespace with many smaller files each under 2
> gigabytes.
It's completely different, actually. Data Files are the smallest unit of backup, not tablespaces. So I can back up part of a tablespace on Monday, part on Tuesday, part on Wednesday. You, with your single multi-gigabyte data file, must back the entire thing up on the Monday. Better pray that your maintenance window is big enough.
>
> However, the objection presented may really relate to tablespace size.
> If that is the case, I would agree that a well-designed database has
> many tablespaces with one datafile per tablespace. It also would have
> little free space both within extents as well as outside extents in the
> datafiles.
>
I think this is going off the edge into La-la land. Do you work with data warehouses? If so, fine. I can, however, think of nothing worse than for an OLTP database to have 'little free space' available.
> What I suspect is typical with databases that arbitrarily impose the
> 2-gigabyte limit is the following:
>
> (1) Less than 7 tablespaces counting system, temp and rbs.
>
Nope.
> (2) Hundreds of equally sized 2-gigabyte files split among about 3
> tablespaces.
>
Getting ridiculous.
> (3) Lots of free space outside extents in the datafiles due to
> inadequately planned direct-path loads.
>
Ah, you do work in a data warehouse, then.
> (4) Lots of free space within large locally managed tablespace extents.
>
> Is that how your database looks?
>
No Frank, it doesn't. The only negative with having lots and lots (and I'm talking hundreds) of data files is that checkpoint times head towards the ceiling. There are many plusses. You, with your single-file tablespaces, will lose all access to that tablespace's data when that file gets corrupted/deleted/whatever whilst you recover it. I, with my multi-file tablespaces, will lose access to maybe 20% of the data -the bit that is actually stored on the one datafile. The rest of the tablespace is fine to access. You must do large backups in one hit, I have the flexibility to schedule them to suit. You must pray there is never a need to transfer your database design to an operating system that doesn't support such large files; I can transfer my design to any O/S you care to mention. I get automatic striping across my multiple files, and decent I/O distribution, as segments extend; you have all your extents on one file, and must rely on hardware striping. There are others, but they'll do for now. I get better and easier management, and my design has inherent performance advantages (checkpoints willing) -the very two things you tout as advantages for mono-file tablespaces. HJR
> Frank Hubeny
>

>
> Howard J. Rogers wrote:
>
> >Why on Earth you'd *want* a data file bigger than 2Gb beats me! You;ve got
> >to back it up in its entirety, restore it in its entirety, and online and
> >offline it in its entirety. More than 2Gb I would not go!
> >
> >Regards
> >HJR
> >--
> >
> >Oracle Resources : http://www.geocities.com/howardjr2000
> >========================================
> >
> >
> >"Ragnar Hark" <ragnar.hark.no.spam_at_mail.ee> wrote in message
> >news:9rr0sd$fh0$1_at_kadri.ut.ee...
> >
> >>Hei !
> >>I have 20GB reiserfs volume on the RH7.2, block size is 6K , but i cannot
> >>create datafile larger tahn 2GB ?
> >>my Oracle version is 8.1.7 and reiserfs supports filesize over 20GB.
> >>
> >>
> >>
> >>
> >>
> >
> >
>
Received on Sat Nov 03 2001 - 16:22:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US