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

We learned the hard way a lesson about file sizes.  We were using 2GB
files on our DW DB for years and it grew so large that at one point we
had > 32K datafiles and this caused us great pains as negative file_id's
starting showing up for datafiles and we started getting a mismatch
between dba_data_files and v$datafile!!!  In other words, a count(*)
from dba_data_files didn't match count(*) from v$datafile!!!

After contacting Oracle Support we were informed that we had reached the
max # of datafiles limit for a 32-bit Oracle (9.2.0.4.0) and the only
solution was we had to logically move the data in any tablespace which
had a negative file_id to new tablespaces with bigger datafile sizes.
This was very time consuming and very painful, but we completed it.

Lesson learned and since then we've been using 16GB files.

Thanks,
Abdul

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jesse, Rich
Sent: Monday, September 11, 2006 2:14 PM
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: 2GB or not 2GB (datafile limit)? That is the question.

Not me!  :)

Since this is (supposedly) an OLTP-only DB, I set it up with 8K blocks.
I'll be setting max filesizes to 28GB.

Thanks guys for hashing that out!

Rich


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nuno Souto
Sent: Saturday, September 09, 2006 1:07 PM
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: 2GB or not 2GB (datafile limit)? That is the question.

Kevin Closson wrote,on my timestamp of 10/09/2006 3:27 AM:

> 30GB is 
> almost 31.99999237060546875GB ((((2^22)-1)*8KB)/1GB)  :-)

wanna bet if someone pushes a file near that boundary
they'll spring an undocumented bug of some sort?
Me?  I'm staying near 28 or thereabouts.
And sleeping well.  ;-)


-- 
Cheers
Nuno Souto
in rainy Sydney, Australia
dbvision@xxxxxxxxxxxx
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: