The following is a list of database limits which are divided into four categories in addition to the PL/SQL compiler limits. It is an aggregation of 5 separate web pages from the Oracle Database 11g Release 2 documentation library. I put them all here on one page for convenience.
Datatype Limits
| Datatypes | Limit | Comments |
| BFILE | Maximum 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 Comments | The 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. |
| BLOB | Maximum 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). |
| CHAR | Maximum size: 2000 bytes | None |
| CHAR VARYING | Maximum size: 4000 bytes | None |
| CLOB | Maximum 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 characters | None |
| LONG | Maximum size: 2 GB – 1 | Only one LONG column is allowed per table. |
| NCHAR | Maximum size: 2000 bytes | None |
| NCHAR VARYING | Maximum size: 4000 bytes | None |
| NCLOB | Maximum 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). |
| NUMBER | 999…(38 9’s) x10125 maximum value -999…(38 9’s) x10125 minimum value | Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa) |
| Precision | 38 significant digits | None |
| RAW | Maximum size: 2000 bytes | None |
| VARCHAR | Maximum size: 4000 bytes | None |
| VARCHAR2 | Maximum size: 4000 bytes | None |
Logical Database Limits
| Item | Type of Limit | Limit Value |
| GROUP BY clause | 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 |
| Columns | 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 query 255 subqueries in the WHERE clause |
| Partitions | Maximum length of linear partitioning key | 4 KB – overhead |
| Partitions | Maximum number of columns in partition key | 16 columns |
| Partitions | Maximum number of partitions allowed per table or index | 1024K – 1 |
| Rows | Maximum number per table | Unlimited |
| 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. |
| 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 |
Physical 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 | 65533 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 extents (more) | Maximum per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
| Database extents | Maximum per locally managed (uniform) tablespace | 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
| Database file size | Maximum | Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB 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 statement Control 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 | 4 MB |
| Redo Log File Size | Maximum Size | Operating system limit; typically 2 GB |
| Tablespaces | Maximum number per database | 64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file |
| Bigfile Tablespaces | Number of blocks | A 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) Tablespaces | Number of blocks | A 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 file | Maximum size | Dependent on the operating system.An external table can be composed of multiple files. |
Process and Runtime Limits
| Item | Type of Limit | Limit Value |
| 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 |
PL/SQL Compiler Limits
| Item | Limit |
| bind variables passed to a program unit | 32768 |
| exception handlers in a program unit | 65536 |
| fields in a record | 65536 |
| levels of block nesting | 255 |
| levels of record nesting | 32 |
| levels of subquery nesting | 254 |
| levels of label nesting | 98 |
| levels of nested collections | no predefined limit |
| magnitude of a PLS_INTEGER or BINARY_INTEGERvalue | -2147483648..2147483647 |
| number of formal parameters in an explicit cursor, function, or procedure | 65536 |
| objects referenced by a program unit | 65536 |
| 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 |
No comments:
Post a Comment