Thursday, August 30, 2012

Oracle datafile maximum size

To find out how big your datafile can grow, you should first know how much your OS/filesystem supports.

Maximum OS file size:
===============
Maximum OS file size is determined by the type of filesystem where you are creating the file.
On Linux, type of filesystem can be determined using command df -T

e.g. ext3 filesystem supports maximum 2^32 blocks. (http://en.wikipedia.org/wiki/Ext3)

Now how to find size of a OS block (Block size for a filesystem)?
On linux, it can be found using tune2fs or dumpe2fs.
e.g.

dumpe2fs /dev/sda1 | grep -i 'Block size'
Block size:               4096

So,

Maximum ext3 "filesystem" size with 4k blocksize can be 2^32*4k i.e. 16TB
Maximum size of single file created on that filesystem can be 2TB (Haven't figured out how is this limit calculated).


Oracle Database Maximum size for datafiles:
==============================
Reference: Oracle Documentation
11gR2: http://docs.oracle.com/cd/E11882_01/server.112/e25513/limits002.htm#i287915

It depends on:
1. Database block size (db_block_size)
2. Maximum database blocks per datafile. This is platform dependent.
3. Limited by maximum operating system file size

For smallfile tablespaces:
11gR2 : Operating system dependent. Limited by maximum operating system file size; typically 2^22(i.e. 4194304) or 4 MB blocks
i.e. for 8k db_block_size database, the maximum size of the datafile on ext3(4k OS block size) filesystem could be 32GB.

For bigfile tablespaces:
11gR2: Single datafile upto 2^32 blocks i.e. for 8k db_block_size, the maximum size of bigfile tablespace/datafile would be 32TB. But ext3(4k OS block size) file/filesystem would not support this big datafile. And it would be limited to 2TB for ext3 filesystem.