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

  • From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <wbfergus@xxxxxxxx>, <Rich.Jesse@xxxxxx>
  • Date: Fri, 8 Sep 2006 10:43:09 -0400

I also do not think there is a limit any longer.  The only self-imposed
limit I consider is file and space management.  If your mount point is,
say, 80G, then creating files to fit is my only consideration.  I would
consider multiple 10G files rather than an 80Gig file just so that I can
manage the files across files systems a little easier.  Easier to move
them around if I need to.


This transmission may contain confidential, proprietary, or privileged 
information which is intended solely for use by the individual or entity to 
whom it is addressed.  If you are not the intended recipient, you are hereby 
notified that any disclosure, dissemination, copying or distribution of this 
transmission or its attachments is strictly prohibited.  In addition, 
unauthorized access to this transmission may violate federal or State law, 
including the Electronic Communications Privacy Act of 1985.  If you have 
received this transmission in error, please notify the sender immediately by 
return e-mail and delete the transmission and its attachments.


From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of William B Ferguson
Sent: Friday, September 08, 2006 10:30 AM
To: Rich.Jesse@xxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: 2GB or not 2GB (datafile limit)? That is the question.


I don't think so. The only real reason I can see would be it's easier
and faster to restore two or three 'corrupt' files of 2GB than a whole
100GB file, but in the Oracle environment, that probably doesn't make
any difference, as you'd still probably need to restore all of the
associated 2 GB files for that tablespace, and multiple small files take
longer than one large file. 

It might make a difference with many disks, as you could have the
smaller files (even if they were 25 GB), spread across multiple disks,
so multiple reads could be accomplished faster, instead of back and
forth within the same file on the same disk (or am I dating myself

                              Bill Ferguson
           U.S. Geological Survey - Minerals Information Team
                          PO Box 25046, MS-750
                          Denver Federal Center
                         Denver, Colorado 80225
          Voice (303)236-8747 ext. 321     Fax   (303)236-4208
     ~ Think on a grand scale, start to implement on a small scale ~ 

"Jesse, Rich" <Rich.Jesse@xxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 

09/08/2006 08:16 AM 

Please respond to






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




Many moons ago, way back in the 32-bit era when Y2K was a looming
nightmare, I had instituted a policy that no Oracle datafile would be
setup to grow larger than 2GB.  This was due to some known bugs with
files larger than 2GB on many platforms/filesystems at the time.

As I'm now looking at a vendor's ERP installation, I was about to reduce
their max datafile size from 32GB to 2GB when I asked myself "Why?".  Is
there any valid sane reason to do this anymore?  I do not expect the DB
size to grow beyond a modest 100GB in the next two years.  The server is
an IBM P5 blade running AIX5.3 and using JFS filesystems.  Other similar
servers with other DBs (e.g. Sybase) currently handle db files in the
100's of GB with no problem.

I don't see any need to limit the datafile size to 2GB anymore.  Anyone


Other related posts: