4
Database Limits

This chapter lists the limits of values associated with database functions and objects. The following topic is included in this chapter:

Database Limits

Database Limits

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. For more information on the maximum value of such limits, see your operating system-specific Oracle documentation.

Table 4-1 lists datatype limits

Table 4-2 lists physical database limits

Table 4-3 lists logical database limits

Table 4-4 lists process/runtime limits

Table 4-1 Datatype Limits

Datatypes	Limit	Comments
BFILE	maximum size: 4 GB maximum size of file name: 255 characters maximum size of directory name: 30 characters maximum number of open BFILEs: see comments	The maximum number of BFILEs is limited by the value of SESSION_MAX_OPEN_FILES, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	4 GB maximum	The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)
CHAR	2000 bytes maximum
CHAR VARYING	4000 bytes
CLOB	4 GB maximum	The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)
Literals (characters or numbers in SQL or PL/SQL)	4000 characters maximum
LONG	2³¹-1 bytes (2 GB) maximum	Only one LONG column allowed per table
NCHAR	2000 bytes
NCHAR VARYING	4000 bytes
NCLOB	4 GB maximum	The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)
NUMBER	999...(38 9's) x10¹²⁵maximum value	Can be represented to full 38-digit precision (the mantissa).
NUMBER	-999...(38 9's) x10¹²⁵minimum value	Can be represented to full 38-digit precision (the mantissa).
Precision	38 significant digits
RAW	2000 bytes maximum
VARCHAR	4000 bytes maximum
VARCHAR2	4000 bytes maximum

Table 4-2 Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	minimum	2048 bytes; must be a multiple of O/S physical block size
Database Block Size	maximum	O/S-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 controlfiles	1 minimum: 2 or more (on separate devices) strongly recommended
Controlfiles	size of a controlfile	dependent on O/S and database creation options; maximum of 20,000 x (database block size)
Database files	maximum per tablespace	O/S 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 init parameter for a particular instance
Database file size	maximum	O/S dependent, limited by maximum O/S file size; typically 2²² or 4M blocks
MAXEXTENTS	default value	derived from tablespace default storage or DB_BLOCK_SIZE
MAXEXTENTS	maximum	unlimited
Redo Log Files	maximum number of logfiles	LOG_FILES initialization parameter, or MAXLOGFILES in CREATE DATABASE; controlfile can be resized to allow more entries; ultimately an O/S limit
Redo Log Files	maximum number of logfiles per group	unlimited
Redo Log File Size	minimum size	50K bytes
Redo Log File Size	maximum size	O/S limit, typically 2GB
Tablespaces	maximum number per database	64K Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file.

Table 4-3 Logical Database Limits

Item	Type	Limit
GROUP BY clause	maximum length	The group-by expression and all of the non-distinct aggregates (e.g., sum, avg) need to fit within a single database block.
Indexes	maximum per table	unlimited
Indexes	total size of indexed column	40% of the database block size minus some overhead.
Columns	table	1000 columns maximum
Columns	indexed (or clustered index)	32 columns maximum
	bitmapped index	30 columns maximum
	bitmapped index	30 columns maximum
Constraints	maximum per column	unlimited
Nested Queries	maximum number	255
Partitions	maximum length of linear partitioning key	4KB - overhead
	maximum number of columns in partition key	16 columns
	maximum number of partitions allowed per table or index	64K-1 partitions
Rollback Segments	maximum number per database	no limit; limited within a session by MAX_ROLLBACK_SEGMENTS init parameter
Rows	maximum number per table	no limit
SQL Statement Length	maximum length of statements	64K 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. Consult your PL/SQL or Developer/2000 documentation for details. The limits typically range from 2000-3000 lines of code.
Trigger Cascade Limit	maximum value	O/S dependent, typically 32
Users and Roles	maximum	2,147,483,638
Tables	maximum per clustered table	32 tables
Tables	maximum per database	unlimited

Table 4-4 Process / Runtime Limits

Item	Type	Limit
Instances per database	maximum number of OPS instances per database	O/S dependent
Locks	row-level	unlimited
Locks	Distributed Lock Manager	O/S dependent
SGA size	maximum value	O/S dependent, typically 2-4 GB for 32-bit O/S, > 4 GB for 64 bit O/S
Advanced Queuing Processes	maximum per instance	10
Job Queue Processes	maximum per instance	36
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	32K, limited by PROCESSES and SESSIONS init parameters
LCK Processes	maximum per instance	10
MTS Servers	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.
Dispatchers	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.
Parallel Execution Slaves	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.
Backup Sessions	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.

4Database Limits