Each Oracle datafile can contain maximum (2^22) i.e., 4194303 (4 Million) data blocks. So maximum file size is 4194303 multiplied by the database block size.
In a database there can have maximum of 65533 data files.
In database, db_block_size can have 2K, 4K, 8K, 16K and 32K
SQL> show parameter db_block_size; gives your data 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 (2^32) i.e., 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
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 (2^32) i.e., 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
Maximum database size= maximum datafile size * maximum datafile can be in a database.
So maximum data file and database size depends on data block size
Great post! Just wanted to add that the maximum number of data blocks per data file is more specifically represented by 2^22-1 which is 4194303.
ReplyDelete