Physical Logical Oracle Database Limits

March 15, 2011 eMarcel Oracle, 0

Physical Oracle 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 20,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 systemsLimited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum	4 GB, regardless of the maximum file size allowed by the operating system
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 22² or 4M 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	50 KB
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, as each tablespace must include at least one file

Logical Database Limits

Item	Type	Limit
GROUP BYclause	Maximum length	The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.
Indexes	Maximum per table	Unlimited
Indexes	total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000 columns maximum
Columns	Per index (or clustered index)	32 columns maximum
	Per bitmapped index	30 columns maximum
Constraints	Maximum per column	Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement	Unlimited in the FROM clause of the top-level query255 subqueries in the WHERE clause
Partitions	Maximum length of linear partitioning key	4 KB – overhead
	Maximum number of columns in partition key	16 columns
	Maximum number of partitions allowed per table or index	64 K-1 partitions
Rollback Segments	Maximum number per database	No limit; limited within a session by the MAX_ROLLBACK_SEGMENTS initialization parameter
Rows	Maximum number per table	Unlimited
SQL Statement Length	Maximum length of statements	64 K maximum; particular tools may impose lower limits
Stored Packages	Maximum size	PL/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.See Also: Your PL/SQL or Developer/2000 documentation for details
Trigger Cascade Limit	Maximum value	Operating system-dependent, typically 32
Users and Roles	Maximum	2,147,483,638
Tables	Maximum per clustered table	32 tables
Tables	Maximum per database	Unlimited

Process and Runtime Limits

Item	Type	Limit
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	10
Job Queue Processes	Maximum per instance	1000
I/O Slave Processes	Maximum per background process (DBWR, LGWR, etc.)	15
I/O Slave Processes	Maximum per Backup session	15
Sessions	Maximum per instance	32 KB; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	Maximum per instance	10
Shared Servers	Maximum per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance