Tuesday 9 March 2010

Physical Database Limits(11g)

Physical Database Limits(11g)

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 20,000 x (database block size)
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533

May 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 statement

Control 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 K

Number 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.

Datatype Limits

DatatypesLimitComments
BFILEMaximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see CommentsThe maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOBMaximum size: (4 GB – 1) *DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
CHARMaximum size: 2000 bytesNone
CHAR VARYINGMaximum size: 4000 bytesNone
CLOBMaximum size: (4 GB – 1) *DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
Literals (characters or numbers in SQL or PL/SQL – more)Maximum size: 4000 charactersNone
LONGMaximum size: 2 GB – 1Only one LONG column is allowed per table.
NCHARMaximum size: 2000 bytesNone
NCHAR VARYINGMaximum size: 4000 bytesNone
NCLOBMaximum size: (4 GB – 1) *DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
NUMBER999…(38 9’s) x10125 maximum value -999…(38 9’s) x10125minimum valueCan be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision38 significant digitsNone
RAWMaximum size: 2000 bytesNone
VARCHARMaximum size: 4000 bytesNone
VARCHAR2Maximum size: 4000 bytesNone

Logical Database Limits

ItemType of LimitLimit Value
GROUP BY clauseMaximum lengthThe GROUP BY expression and all of the nondistinct aggregate functions (for example,SUM, AVG) must fit within a single database block.
IndexesMaximum per tableUnlimited
IndexesTotal size of indexed column75% of the database block size minus some overhead
ColumnsPer table1000 columns maximum
ColumnsPer index (or clustered index)32 columns maximum
ColumnsPer bitmapped index30 columns maximum
ConstraintsMaximum per columnUnlimited
SubqueriesMaximum levels of subqueries in a SQL statementUnlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause
PartitionsMaximum length of linear partitioning key4 KB – overhead
PartitionsMaximum number of columns in partition key16 columns
PartitionsMaximum number of partitions allowed per table or index1024K – 1
RowsMaximum number per tableUnlimited
Stored PackagesMaximum sizePL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.
Trigger Cascade LimitMaximum valueOperating system-dependent, typically 32
Users and RolesMaximum2,147,483,638
TablesMaximum per clustered table32 tables
TablesMaximum per databaseUnlimited

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 20,000 x (database block size)
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533 May be less on some operating systems Limited also by size of database blocks and by theDB_FILES initialization parameter for a particular instance
Database extents (more)Maximum 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 orDB_BLOCK_SIZE initialization parameter
MAXEXTENTSMaximumUnlimited
Redo Log FilesMaximum number of logfilesLimited by value of MAXLOGFILES parameter in theCREATE DATABASE statement Control 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 K Number 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.

Process and Runtime Limits

ItemType of LimitLimit Value
Instances per databaseMaximum number of cluster database instances per databaseOperating system-dependent
LocksRow-levelUnlimited
LocksDistributed Lock ManagerOperating system dependent
SGA sizeMaximum valueOperating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems
Advanced Queuing ProcessesMaximum per instance10
Job Queue ProcessesMaximum per instance1000
I/O Slave ProcessesMaximum per background process (DBWR, LGWR, etc.)15
I/O Slave ProcessesMaximum per Backup session15
SessionsMaximum per instance32 KB; limited by the PROCESSES and SESSIONSinitialization parameters
Global Cache Service ProcessesMaximum per instance10
Shared ServersMaximum per instanceUnlimited within constraints set by thePROCESSES and SESSIONS initialization parameters, for instance
DispatchersMaximum per instanceUnlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance
Parallel Execution SlavesMaximum per instanceUnlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance
Backup SessionsMaximum per instanceUnlimited within constraints set by PROCESSESand SESSIONS initialization parameters, for instance

PL/SQL Compiler Limits

ItemLimit
bind variables passed to a program unit32768
exception handlers in a program unit65536
fields in a record65536
levels of block nesting255
levels of record nesting32
levels of subquery nesting254
levels of label nesting98
levels of nested collectionsno predefined limit
magnitude of a PLS_INTEGER or BINARY_INTEGERvalue-2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure65536
objects referenced by a program unit65536
precision of a FLOAT value (binary digits)126
precision of a NUMBER value (decimal digits)38
precision of a REAL value (binary digits)63
size of an identifier (characters)30
size of a string literal (bytes)32767
size of a CHAR value (bytes)32767
size of a LONG value (bytes)32760
size of a LONG RAW value (bytes)32760
size of a RAW value (bytes)32767
size of a VARCHAR2 value (bytes)32767
size of an NCHAR value (bytes)32767
size of an NVARCHAR2 value (bytes)32767
size of a BFILE value (bytes)4G * value of DB_BLOCK_SIZE parameter
size of a BLOB value (bytes)4G * value of DB_BLOCK_SIZE parameter
size of a CLOB value (bytes)4G * value of DB_BLOCK_SIZE parameter
size of an NCLOB value (bytes)4G * value of DB_BLOCK_SIZE parameter


Physical Database Limits(10g)

ItemType of LimitLimit Value
Database Block SizeMinimum2048 bytes; must be a multiple of operating system physical block size
MaximumOperating system dependent; never more than 32 KB
Database BlocksMinimum in initial extent of a segment.2 blocks
Maximum per datafilePlatform dependent; typically 222 - 1 blocks
ControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommended
Size of a control fileDependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Maximum per database65533

May 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)
Maximum 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
MaximumUnlimited
Redo Log FilesMaximum number of logfilesLimited by value of MAXLOGFILES parameter in the CREATE DATABASE statement

Control file can be resized to allow more entries; ultimately an operating system limit

Maximum number of logfiles per groupUnlimited
Redo Log File SizeMinimum size50 KB
Maximum sizeOperating system limit; typically 2 GB
TablespacesMaximum number per database64 KB

Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file

Bigfile TablespacesNumber of blocks232 (4 GB) blocks
Smallfile (traditional) TablespacesNumber of blocks222 (4 MB) blocks

No comments: