Re: Datafile size - Is bigger better?

  • From: "Stephen Booth" <>
  • To: jaykash@xxxxxxxxxxx
  • Date: Wed, 15 Nov 2006 08:12:05 +0000

On 14/11/06, JayDBA <jaykash@xxxxxxxxxxx> wrote:

Can I trigger a discussion on the pros and cons of datafile max-size. I am
in a shop where the datafile size is limited to 2gb on a 64-bit platform
(Raided) and I am trying to weigh the pros and cons of having a larger file

A few things come to mind.

Never create a file bigger than the smallest backup media you might
want to back it up to.  Seems pretty obvious, "Can't get a quart in a
pint pot", but I still see people getting caught out by it.  The most
common is that the files fit fine onto their normal backup media but
their "Emergency backup backup system" (i.e. what they use when their
primary system is broken/out of service/being maintained/whatever) is
the previous generation and doesn't quite have the capacity.

Bigger files means more to move if you need to redistribute some files
for performance or other reasons.  There's a strong movement,
especially amongst people who listen to SAN salesdroids, that says we
don't need to worry about where the files go, the SAN will take care
of it.  Horse puckey.  On some sites we're now having to talk about
distributing files accross SANs, or at least different SAN
connections, for performance.

Related to the above two, if you're using transportable tablespaces
and are sending out the files on physical media (e.g. CD-R or DVD-ROM)
then obviously your files have to be able to fit on the media you're

Some OSes (or to be precise filesystems supported by OSes) still have
issues with files over 2GB.  Others with files over 128Gb, 256GB,
512Gb or 1Tb.  The second most common reason for a restore of a file
having to be done where I work (the first is users deleting files) is
it growing to over 2Gb under Windows and getting corrupted because the
file system can't handle files over 2Gb.  Last week I went on a
training course in AIX 5.3 (we're converting from Solaris) that
included a session on the problems AIX has with files over 2Gb in
size.  That's the current version of AIX, 64bit, and it still has
issues with files over 2Gb.  There are ways to get it to support files
over 2Gb (setting a flag when you create the file system) but that
requires your sysadmin/storage admin to correctly configure the file
systems.  More complexity and relies on someone who may not understand
what you're trying to do and may have other things of greater personal
importance driving them (e.g. beancounters yelling at them for buying
loads of fast and smaller but expensive disks rather than fewer slow,
big but cheap disks).


It's better to ask a silly question than to make a silly assumption.

Other related posts: