Oracle Database Physical Limits from Oracle 7 to Oracle 11gr2 --- You may not know

Dear All,

Today i thought to write something about Oracle Database Physical Limits ... from Oracle 7 to Oracle 11gr2 what are the changes happened and what are the new features introduced.

Lets start with Oracle 7 Version

Item	Type	Limit
blocks (Oracle7)	minimum in initial extent	2 blocks (automatically enforced)
	maximum	232 -1 (up to 4 terabytes, depending on block size)
control files	number of control files	one minimum: 2 or more strongly recommended on separate devices
	size of a control file	typically 50..200Kb, depending on database creation options; maximum is O/S-dependent
database files	system	1022 or value of DB_FILES in INIT.ORA, or limited by value of MAXDATAFILES in CREATE DATABASE. Less on some operating systems.
database file size	minimum	no absolute limit except for first file whose minimum size is 2 MB
	maximum	O/S dependent, typically 16 million Oracle7 blocks
redo log files	database	255 or value for LOG_FILES in INIT.ORA, or by MAXLOGFILES in CREATE DATABASE. Ultimately, an operating system limit.
redo log file size	minimum	50 Kbytes
tablespaces	database	no limit

Above are the basic list of all items started with Oracle 7 , now we check changes in each higher release of oracle.

Oracle 8i

In this release the below new item introduced and  improved.

MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter.
	Maximum	Unlimited

Database extents

Maximum

2 GB, regardless of the maximum file size allowed by the operating system.

Oracle 9ir1

There is no changes between oracle 8i to 9ir1.

Oracle 9ir2

There is no changes between oracle 9ir1 to 9ir2.

Oracle 10gr1

In this release the below new item introduced and  improved.

Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)

Bigfile Tablespaces	Number of blocks	232 (4 GB) blocks
Smallfile (traditional) Tablespaces	Number of blocks	222 (4 MB) blocks

Oracle 10gr2

In this release the below new item introduced and improved.

Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

Oracle 11gr1

There is no changes between oracle 10gr2 to 11gr1.

Oracle 11gr2

There is no changes between oracle 11gr1 to 11gr2.

Oracle 11gr2 

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of 25,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 KNumber of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Maximum size	Dependent on the operating system.An external table can be composed of multiple files.