Maximum datafile size limits...

Max. Number of Data Files in 9i and 10g is limited to 65,536.

Max. Number of blocks in a data file is:

4,194,304 (4 million) in 9i and increased to

4,294,967,296 (4 billions) in 10g when used BIGFILE Tablespace.

How to calcualte Max.Datafile Size:

Max.DataFile Size = db_block_size * Max. Number of Blocks

How to calculate Max.Database Size:

Max.Data file Size * Max. Number of Datafiles.

Here is the chart with SMALLFILE Tablespace (using 4 million blocks):

Block Size Max. Data File Size Max. Database Size

32 K 128 GB 8,388,608 GB

16 K 64 GB 4,194,304 GB

8 K 32 GB 2,097,152 GB

4 K 16 GB 1,048,579 GB

2 K 8 GB 524,288 GB

Here is the chart with BIGFILE Tablesapce (using 4 million blocks):

Block Size Max. Data File Size Max. Database Size

32 K 131,072 GB (128T) 8,589,934,592 GB

16 K 65,536 GB (64T) 4,294,967,296 GB

8 K 32,768 GB (32T) 2,147,483,648 GB

4 K 16,384 GB (16T) 1,073,741,824 GB

2 K 8,192 GB (8T) 536,870,912 GB

The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.

Note:

The BIGFILE syntax must be specified during the tablespace creation as such:

CREATE BIGFILE TABLESPACE my_ts;

The BIGFILE tablespace can ONLY have a SINGLE datafile.

By creating a tablespace using this syntax, Oracle increases the maximum number of blocks in a datafile from the 4 Million blocks to a maximum of 4 Billion.