A Database Limits

This appendix lists the limits of values associated with database functions and objects. Limits exist on several levels in the database. There is usually a hard-coded limit in the database that cannot be exceeded. This value may be further restricted for any given operating system.

Database limits are divided into four categories:

Datatype Limits
Physical Database Limits
Logical Database Limits
Process and Runtime Limits

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^Foot 1).
`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^Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	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^Footref 1).
`NUMBER`	999...(38 9's) x10¹²⁵ maximum value -999...(38 9's) x10¹²⁵ 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

^Footnote 1The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates 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
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 2²² - 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	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 2²² 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 ( 2³² ) 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 (2²²) 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 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. 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
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

Note:

When an object instance exists in memory, there is no fixed limit on the number of attributes in the object. But the maximum total amount of memory consumed by an object instance is 4 GB.When an object instance is inserted into a table, the attributes are exploded into separate columns in the table, and the Oracle 1000-column limit applies.

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