Oracle8 Reference Release 8.0 A58242-01 |
Library | Product | Contents | Index

# 5
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 5-1 lists datatype limits

Table 5-2 lists physical database limits

Table 5-3 lists logical database limits

Table 5-4 lists process/runtime limits

##### Table 5-1 Datatype Limits

Datatypes | Limit | Comments
--- | --- | ---
BFILE | maximum size: 4 GBmaximum size of file name: 255 charactersmaximum size of directory name: 30 charactersmaximum 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 | 231-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) x10125maximum value | Can be represented to full 38-digit precision (the mantissa).
-999...(38 9's) x10125minimum 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 5-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
maximum | O/S-dependent; never more than 32 KB
Database Blocks | minimum in initial extent of a segment | 2 blocks
maximum per datafile | platform dependent; typically 222 blocks
Controlfiles | number of controlfiles | 1 minimum: 2 or more (on separate devices) strongly recommended
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
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 222 or 4M blocks
MAXEXTENTS | default value | derived from tablespace default storage or DB_BLOCK_SIZE
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
maximum number of logfiles per group | unlimited
Redo Log File Size | minimum size | 50K bytes
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 5-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
total size of indexed column | 40% of the database block size minus some overhead.
Columns | table | 1000 columns maximum
indexed (or clustered index) | 32 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
maximum per database | unlimited

##### Table 5-4 Process / Runtime Limits

Item | Type | Limit
--- | --- | ---
Instances per database | maximum number of OPS instances per database | O/S dependent
Locks | row-level | unlimited
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
Job Queue Processes | maximum per instance | 36
I/O Slave Processes | maximum per background process (DBWR, LGWR, etc.) | 15
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 Query 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.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index