Each Oracle datafile can contain maximum 4194303 (4 Million) data blocks. So maximum file size is 4194303 multiplied by the database block size.
Block Size | Maximum Datafile Size
———————————————
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB
In Oracle Database 10g, BIGFILE tablespace was introduced. The BIGFILE tablespace can ONLY have a single datafile, but this datafile can contain maximum 4294967295 (4 billion) data blocks.
Block Size | Maximum Datafile Size
———————————————
2k 4294967295 * 2k = 8 TB
4k 4294967295 * 4k = 16 TB
8k 4294967295 * 8k = 32 TB
16k 4294967295 * 16k = 64 TB
32k 4294967295 * 32k = 128 TB
Tasmia Faruq
Diego