Oracle7 Server Reference Manual Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Database Limits

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

Setting Limits

You can set limits when you create a database. These limits are recorded in the control file and cannot be overridden during the life of the database. Also, you can set limits for the duration of an instance with initialization parameters. These parameters temporarily override the database limits of the control file.

For example, the maximum number of database files allowed by the Oracle7 Server can be reduced for a particular database by specifying a lower value for the MAXDATAFILES option of the CREATE DATABASE command. This limit is then recorded in the control file and cannot be exceeded for the life of that database. You can alter the value of the initialization parameter DB_FILES to a value less than MAXDATAFILES for a particular instance. The maximum number of database files is then limited to the number specified by DB_FILES for the life of that instance.

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 232 -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 231 -1 characters (2 gigabytes) maximum VARCHAR2 column 2000 characters maximum
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.0x10125
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

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index