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.
