Oracle7 Server Reference Manual

Database Limits

Limits exist on several levels in the database. There is usually a hard-coded limit in the database that cannot be exceeded. The 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 5 - 1 lists types and limits for database functions and options.

Item Type Limit
blocks (Oracle7)
minimum in initial extent 2 blocks (automatically enforced)
maximum 2³²-1 (up to 4 terabytes, depending on block size)
characters CHAR column index 255 characters maximum no absolute limit, but a function of block size LONG column 2³¹ -1 characters (2 gigabytes) maximum VARCHAR2 column 2000 characters maximum
columns
LONG columns index (or cluster index) table expression list view definition 16 columns maximum 254 columns maximum 254 columns maximum 254 columns maximum
table 1 LONG column per table
constraints CHECK (on columns) unlimited
context area size no absolute limit (1024 is the minimum initial extent size)
control files number of control files one minimum: 2 or more strongly recommended on separate devices
size of a control file typically 50..200Kb, depending on database creation options; maximum is O/S-dependent
database files system 1022 or value of DB_FILES in INIT.ORA, or limited by value of MAXDATAFILES in CREATE DATABASE. Less on some operating systems.
database file size minimum no absolute limit except for first file whose minimum size is 2 MB
maximum O/S dependent, typically 16 million Oracle7 blocks
GROUP BY clause maximum size number of bytes limited to one Oracle7 block, less O/S-dependent block overhead, less 2-bytes per group-by expression, less one of the following: 2 bytes plus size of each aggregate of a non-distinct value Example: COUNT(DISTINCT(x)) or two bytes plus size in bytes of the longest aggregate of a distinct value Example: COUNT(x)
indexes table no limit
total size of indexed columns one-half the Oracle7 block size minus some overhead
instances parallel server O/S dependent, subject to Oracle7 limit of 255
literals character string number (+ or -) 255 characters (10E-135 to 10E125)
locks transaction distributed no limit; O/S dependent
MAXEXTENTS derived from DB_BLOCK_SIZE O/S dependent
nested queries 255 queries
NUMBER maximum value 1.0x10¹²⁵
precision up to 38 significant digits per numeric value
redo log files database 255 or value for LOG_FILES in INIT.ORA, or by MAXLOGFILES in CREATE DATABASE. Ultimately, an operating system limit.
redo log file size minimum 50 Kbytes
rollback segments database no limit
rows table no limit
SGA size maximum no limit
SQL statement length 64 K maximum length; particular tools may have lower limits
stored packages size SQL*FORMS may have limits on the size of stored procedures you can call. Consult your SQL*Forms documentation for details.
tablespaces database no limit
tables cluster database 32; no limit
trigger cascade limit maximum 32, larger values O/S-dependent
users and roles maximum 65525 (users and roles combined)

Table 5 - 1. (continued) Types and Limits for Database Functions and Objects

Item	Type	Limit
blocks (Oracle7)	minimum in initial extent	2 blocks (automatically enforced)
	maximum	2³²-1 (up to 4 terabytes, depending on block size)
characters	CHAR column index	255 characters maximum no absolute limit, but a function of block size LONG column 2³¹ -1 characters (2 gigabytes) maximum VARCHAR2 column 2000 characters maximum
columns LONG columns	index (or cluster index) table expression list view definition	16 columns maximum 254 columns maximum 254 columns maximum 254 columns maximum
	table	1 LONG column per table
constraints	CHECK (on columns)	unlimited
context area	size	no absolute limit (1024 is the minimum initial extent size)
control files	number of control files	one minimum: 2 or more strongly recommended on separate devices
	size of a control file	typically 50..200Kb, depending on database creation options; maximum is O/S-dependent
database files	system	1022 or value of DB_FILES in INIT.ORA, or limited by value of MAXDATAFILES in CREATE DATABASE. Less on some operating systems.
database file size	minimum	no absolute limit except for first file whose minimum size is 2 MB
	maximum	O/S dependent, typically 16 million Oracle7 blocks
GROUP BY clause	maximum size	number of bytes limited to one Oracle7 block, less O/S-dependent block overhead, less 2-bytes per group-by expression, less one of the following: 2 bytes plus size of each aggregate of a non-distinct value Example: COUNT(DISTINCT(x)) or two bytes plus size in bytes of the longest aggregate of a distinct value Example: COUNT(x)
indexes	table	no limit
	total size of indexed columns	one-half the Oracle7 block size minus some overhead
instances	parallel server	O/S dependent, subject to Oracle7 limit of 255
literals	character string number (+ or -)	255 characters (10E-135 to 10E125)
locks	transaction distributed	no limit; O/S dependent
MAXEXTENTS		derived from DB_BLOCK_SIZE O/S dependent
nested queries		255 queries
NUMBER	maximum value	1.0x10¹²⁵
precision		up to 38 significant digits per numeric value
redo log files	database	255 or value for LOG_FILES in INIT.ORA, or by MAXLOGFILES in CREATE DATABASE. Ultimately, an operating system limit.
redo log file size	minimum	50 Kbytes
rollback segments	database	no limit
rows	table	no limit
SGA size	maximum	no limit
SQL statement length		64 K maximum length; particular tools may have lower limits
stored packages	size	SQLFORMS may have limits on the size of stored procedures you can call. Consult your SQLForms documentation for details.
tablespaces	database	no limit
tables	cluster database	32; no limit
trigger cascade limit	maximum	32, larger values O/S-dependent
users and roles	maximum	65525 (users and roles combined)