Oracle March 15, 2011 0

Physical Logical Oracle Database Limits

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
Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment. 2 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
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
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 222 or 4M blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
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
Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 50 KB
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
total size of indexed column 75% of the database block size minus some overhead
Columns Per table 1000 columns maximum
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
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
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
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

 

References: Oracle 9i docs.