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
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.
|
