Search This Blog

Wednesday, September 7, 2011

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

ItemType of LimitLimit Value
Database Block SizeMinimum2048 bytes; must be a multiple of operating system physical block size
Database Block SizeMaximumOperating system dependent; never more than 32 KB
Database BlocksMinimum in initial extent of a segment2 blocks
Database BlocksMaximum per datafilePlatform dependent; typically 222 - 1 blocks
ControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommended
ControlfilesSize of a control fileDependent on operating system and database creation options; maximum of 25,000 x (database block size)
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533May 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 extentsMaximum per dictionary managed tablespace4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extentsMaximum per locally managed (uniform) tablespace2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file sizeMaximumOperating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTSDefault valueDerived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTSMaximumUnlimited
Redo Log FilesMaximum number of logfilesLimited 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 FilesMaximum number of logfiles per groupUnlimited
Redo Log File SizeMinimum size4 MB
Redo Log File SizeMaximum SizeOperating system limit; typically 2 GB
TablespacesMaximum number per database64 KNumber of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile TablespacesNumber of blocksA 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) TablespacesNumber of blocksA 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 fileMaximum sizeDependent on the operating system.An external table can be composed of multiple files.

1 comment:

Anonymous said...

Very nice article

Prachi