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.
By continuing to use the site, you agree to the use of cookies. more information
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.