Datatype Limits
| Datatypes | Limit | Comments |
|---|---|---|
BFILE |
Maximum size: 4 GBMaximum size of a file name: 255 charactersMaximum size of a directory name: 30 charactersMaximum 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, 1000Foot 1 ). |
CHAR |
Maximum size: 2000 bytes | |
CHAR VARYING |
Maximum size: 4000 bytes | |
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, 1000Foot 1). |
| Literals (characters or numbers in SQL or PL/SQL) | Maximum size: 4000 characters | |
LONG |
Maximum size: 2 GB - 1 | Only one LONG column is allowed per table. |
NCHAR |
Maximum size: 2000 bytes | |
NCHAR VARYING |
Maximum size: 4000 bytes | |
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, 1000Foot1). |
NUMBER |
999...(38 9's) x10125 maximum value | Can be represented to full 38-digit precision (the mantissa). |
| -999...(38 9's) x10125 minimum value | Can be represented to full 38-digit precision (the mantissa). | |
| Precision | 38 significant digits | |
RAW |
Maximum size: 2000 bytes | |
VARCHAR |
Maximum size: 4000 bytes | |
VARCHAR2 |
Maximum size: 4000 bytes |
Footnote 1 The absolute maximum numberof columns in a table is 1000. However, when you create an object table(or a relational table with columns of object, nested table, varray, orREF type), Oracle maps the columns of the user-definedtypes to relational columns, creating in effect hidden columns thatcount toward the 1000-column limit. For details on how Oraclecalculates the total number of columns in such a table, refer to Oracle Database Administrator's Guide.
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 |
| 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 per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
| 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 |
| Maximum | Unlimited | |
| Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASEstatementControl 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 | 64KBNumber of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file |
| Bigfile Tablespaces | Number of blocks | 232 (4 GB) blocks |
| Smallfile (traditional) Datafile | Number of blocks | 222 (4 MB) blocks |
| External Tables file | Maximum size | Dependent on the operating system.An external table can be composed of multiple files. |
Logical Database Limits
| Item | Type | Limit |
|---|---|---|
CREATE MATERIALIZED VIEW definition |
Maximum size | 64K Bytes |
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 |
| 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 FROMclause 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 | 1024K- 1 partitions |
|
| 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 3000lines 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 |
Note:The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory
- 从12c以上，partitions为1024k-1，System Change Numbers (SCNs)为(2**64-2**48)
Note:
When an object instance exists in memory, there is no fixed limit onthe number of attributes in the object. But the maximum total amount ofmemory consumed by an object instance is 4 GB.When an object instanceis inserted into a table, the attributes are exploded into separatecolumns in the table, and the Oracle 1000-column limit applies.
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 Secondary Processes | Maximum per background process (DBWR, LGWR, etc.) | 15 |
| Maximum per Backup session | 15 |
|
| Sessions | Maximum per instance | 32K; 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 Secondary | 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 |