Re: 2GB or not 2GB (datafile limit)? That is the question.

Kevin Closson wrote,on my timestamp of 10/09/2006 1:16 AM:

>>>>The "largest number of Oracle blocks per file" limit still 
>>>>applies.  That's around 30GB with 8K block sizes.


> 

>                     ^^^^^^^^^^^^

Actually, like I said in a precise fashion it is around 30GB. Either that or it's pure imagination that my 9i dbs are running on multiple 28GB files!

See here:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch43.htm#287916

The limit is 2^22 -1 , not 2^21 - 1.
The reason I say "around" is that I'm not game to argue the last GB with Oracle calculations or be caught in a boundary bug of some undocumented sort.

Just like the many we had with the 2GB limit a few years ago, where it was pot luck which bug you'd hit first: a doco one or a software one...

> A 10g BIGFILE tablespace supports 2^32 blocks which is 32TB
> for a single file tablespace...they are limited to 1 file per
> tablespace.

That I think is its only problem. There may well be constraints in backups of files of that size. I'd much have preferred that Oracle removed the "one file per tablespace" restriction.

> The whole topic is noise really, modern Oses handle axtremely large
> numbers of file descriptors without issue... so choose what makes
> sense... our QA tests here include OLTP stress tests with databases
> that consist of 16383 datafiles "evenly" distributed throughout
> 512 filesystems...just for bounds testing...

Not really. The number of files can still be very much an issue. Either one goes for one file per tablespace in 10g and one better have the right hardware/software combo to backup multi-TB single files to tape, or one better be prepared for nurturing a bucket load of files and their naming. Again: only for very large dbs. There is no mid-term, which I'd much prefer.

-- 
Cheers
Nuno Souto
in rainy Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l