Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Datafile size - Is bigger better?

Re: Datafile size - Is bigger better?

From: Stephen Booth <>
Date: Wed, 15 Nov 2006 08:12:05 +0000
Message-ID: <>

On 14/11/06, JayDBA <> 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
> limit.

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 using.

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.
Received on Wed Nov 15 2006 - 02:12:05 CST

Original text of this message