Size of Datafiles

[Michael42]

Hello all,

In a SR\TAR I have confirmed with Oracle that there are no compelling technical reasons that datafiles, given that an OS supports it, have to be limited to 5 gigabytes or less.

In Oracle 10g using Bigfile Tablespaces the size of a datafile can now be measured in Terabytes. In both 9i and 10g the limiting factor of course is the block size.

To ease the mangement of a very large database I am using larger and fewer datafiles (50gb each) as in my environment it makes it easier to manage. I am not seeing any performance degredation (probably because of the quality of the equipment).

The above being said, have you found any compelling reasons NOT to use large datafiles?

Thanks for the sanity check,

Michael42

 

[LKBrwnDBA]

NO, not at all.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Michael,

Certainly not an issue in your case, but to avoid the "casual" Tek-Tips reader from shooting her/himself in the foot, for the sake of clarity and completeness: there are still operating-systems versions out there that do not properly accommodate Oracle datafiles that exceed 2GB. The symptom in such cases is that whatever table contains the database block that resides on the 2GB threshold, the data in that table from that threshold block to the end of the table becomes unreadable.

Do avoid personal liability on this issue, I am not going to list all the combinations of operating systems and versions where this is an issue. I will say that we have stumbled onto this very issue on two flavours of *nix operating-system versions.

As a result, we have found a "happy medium" behaviour of allocating as many 2GB-maximum AUTOEXTENDING datafiles as we need for our tablespaces. In doing so, we accrue a variety of benefits (which, for the sake of completeness and fairness, might also exist with a schemes using 2GB+ datafiles):

* Ease of allocating files on file systems with smaller amounts of available free space.
* Ease of accommodating file backups onto file systems with limited space availabilities.
* De facto disk device/controller load balancing.

But, my personal preferences here are, in no way, meant to discourage the use of humongous data files on systems where 2GB files do not introduce data-access risks.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[feherke]

Hi

Mufasa, are you sure the problem is related to the operating system ? I thought the limits are given by the file system, as listed here :

http://en.wikipedia.org/wiki/File_system#Limits

Thanks.

Feherke.

http://rootshell.be/~feherke/

 

[Michael42]

Thanks guys for the feedback.

The OS (Solaris 10) that I have this database on has a multi-Terabyte file size limit. So no issues here.

Any and all comments wlecome on this topic.


Thanks again,

Michael

 

[KenCunningham]

Interesting article, ferherke. I guess the two are essentially interchangeable in this context. The 2Gb limit referred to by Dave was certainly an issue on some older boxes I have worked on. These days too it is sometimes necessary to make filesystems as large file aware to avoid the issue. More information here:

http://en.wikipedia.org/wiki/Large_file_support

 

[feherke]

Hi

Thanks for the clarification, Ken.

Feherke.

http://rootshell.be/~feherke/