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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich Sent: Monday, September 11, 2006 2:14 PM Cc: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto Sent: Saturday, September 09, 2006 1:07 PM Cc: oracle-l_at_freelists.org
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_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


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