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
1 comments:
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.
Post a Comment