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

  • From: JApplewhite@xxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 8 Sep 2006 09:52:05 -0500


I don't know about OS issues, but we also stick to a max 2GB datafile size 
for a different reason.  Almost all our servers (Linux and HPUX) are on 
one of our SANs and we've found that the OSs tend to give each mount point 
the same (on average) fraction of I/O bandwidth to the SANs.  This was 
startlingly obvious when, at the recommendation of one of our SysAdmins, 
we put a Production database all under 1 mount point and its performance 
suffered greatly - no detailed analysis, it was just obvious.  When we 
spread that same database across multiple mount points on the same server 
and SAN, performance improved dramatically.

So, we typically ask for about 16 mount points from about 14GB to 50GB 
each, depending on the server and SAN, and spread or databases evenly 
across those.  Having 2GB datafiles makes it easy to move files around if 
we need to, as well as add datafiles evenly to keep the available space 
about the same on all mount points.

The 2GB limit is, as you've pointed out, due to past problems with larger 
file sizes on some platforms.  We've stuck with it for the newer reasons 
I've outlined above, but there's no magic in the 2GB size other than it 
works nicely for us.

Actually, there's another reason.  We frequently clone our Production 
databases back to Dev and Test on other servers.  With the mount points on 
each server sized equally, we can copy the zipped up datafiles in parallel 
from Prod to their target mount points - saves time.  Our LAN/WAN is all 
fibre, so network bandwidth is rarely an issue.

These may not fit your environment, but they're important to us.

Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk)  /  512.935.5929 (pager)

 I feel so unnecessary.  -- Rufus Thomas
               ( "Do the Funky Chicken")

"Jesse, Rich" <Rich.Jesse@xxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
09/08/2006 09:18 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: