Re: 10g max datafile sizes

On Dec 21, 7:49 am, Ben <benal..._at_yahoo.com> wrote:
> On Dec 20, 4:23 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Dec 20, 4:11 pm, sybra..._at_hccnet.nl wrote:
>
> > > On Thu, 20 Dec 2007 09:35:40 -0800 (PST), Ben <benal..._at_yahoo.com>
> > > wrote:
>
> > > >10.2.0.2 AIX5.2
>
> > > >I was trying to figure out what the maximum size was for datafiles in
> > > >a non bigfile tablespace. I found the following thread on here
> > > >http://tinyurl.com/2bm3nganditlead me to the Physical Database
> > > >Limits page in the documentation.
>
> > > >Am I understanding this correctly that basically a datafile maxsize is
> > > >limited by the size of the database blocks that are being used? So if
> > > >my database is setup for 8K blocksize, then I'm limited to a maximum
> > > >of 32G datafiles? I don't see where it is different between smallfile
> > > >and bigfile tablespaces. Would you need to setup a bigfile tablespace
> > > >with a nonstandard blocksize to accomplish > 32G datafiles?
>
> > > Recovery time anyone?
>
> > > And a bigfile tablespace can only have 1 datafile ever.
>
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
>
> > I do not remember the size range but with a bigfile tablespace you can
> > support at least 128T datafile.  That should take a while to backup
> > and recover.
>
> > Meanwhile a smallfile tablespace can have up to 1022 32G files (if
> > 8k).
> > That should be enough space for most sites.
>
> > -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> I understand the bigfile = 1 datafile thing. Where I didn't see a
> difference is how a bigfile tablespace is supposed to utilize a > 32G
> datafile if your database blocksize is 8K. Do you have to use a
> nonstandard blocksize for a bigfile tablespace?- Hide quoted text -
>
> - Show quoted text -

It has to do with the way Oracle logically breaks up the ROWID for a bigfile tablespace. The format is different in a bigfile tablespace from that used in a smallfile tablespace so Oracle can track more blocks, 32T worth with 8k. Note that Oracle added a bigfile parameter to dbms_rowid with the advent of bigfile tablespace.

HTH -- Mark D Powell -- Received on Sun Dec 23 2007 - 16:26:38 CST