Oracle 9i and 8i Database Limits
Oracle8i ONLY
Advanced Queuing Processes |
maximum per instance |
10 |
Oracle8i ONLY
Job Queue Processes |
maximum per instance
|
36 |
I/O Slave Processes |
maximum per background
process (DBWR, LGWR, etc.) maximum per Backup
session
|
15 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. |
Oracle 9i Limits
Datatype Limits
VARCHAR2 |
Maximum size is 4000 |
NVARCHAR2 |
Maximum size is
determined by the national character set definition, with an upper
limit of 4000 bytes |
NUMBER( p,s) |
The precision p can
range from 1 to 38. The scale s can range from -84 to 127 |
LONG |
up to 2 gigabytes, or
231 -1 bytes |
DATE |
range from |
TIMESTAMP(
fractional_seconds_precision) |
fractional_seconds_precisionis
the number of digits in the fractional part of the SECOND datetime
field. Accepted values of fractional_seconds_precision
are 0 to 9. The default is 6 |
TIMESTAMP(
fractional_seconds_precision) WITH TIME ZONE |
fractional_seconds_precisionis
the number of digits in the fractional part
of the SECOND datetime field. Accepted values are 0 to 9.
The default is 6 |
TIMESTAMP(
fractional_seconds_precision)WITH LOCAL TIME ZONE |
same as TIMESTAMP(
fractional_seconds_precision) WITH TIME ZONE |
INTERVAL YEAR(
year_precision) TO MONTH |
year_precision is the
number of digits in the YEAR datetime field. Accepted values are 0 to
9. The default is 2 |
INTERVAL DAY
(day_precision) TO SECOND ( fractional_seconds_precision) |
day_precision is the
maximum number of digits in the DAY datetime field. Accepted values are
0 to 9. The default is 2 fractional_seconds_precision
is the number of digits in the fractional part of the SECOND field.
Accepted values are 0 to 9. The default is 6 |
RAW( size) |
Maximum size is 2000
bytes |
LONG RAW |
up to 2 gigabytes |
ROWID |
Base 64 string
representing the unique address of a row in its table
|
UROWID [( size)] |
Base 64 string
representing the logical address of a row of an index-organized table.
The optional size is the size of a column of type UROWID. The maximum
size and default is 4000 bytes |
CHAR( size)[BYTE |CHAR] |
Fixed-length character
data of length size bytes.Maximum size is 2000
bytes. Default and minimum size is 1 byte |
NCHAR( size) |
Fixed-length character
data of length size characters. Maximum size is determined by the
national character set definition, with an upper limit of 2000 bytes.
Default and minimum size is 1 character |
CLOB |
Maximum size is 4
gigabytes |
NCLOB |
Maximum size is 4
gigabytes |
BLOB |
Maximum size is 4
gigabytes |
BFILE |
Maximum size is 4
gigabytes |
|
|
|
|
|
|
|
|
Physical Database Limits
Database Block Size |
Minimum Maximum |
2048 bytes; must be a
multiple of operating system physical block size Operating system
dependent; never more than 32 KB |
Database Blocks |
Minimum in initial
extent of a segment Maximum per datafile |
2 blocks Platform dependent;
typically 222-1 blocks |
Controlfiles |
Number of control files Size of a control file |
1 minimum; 2 or more (on
separate devices) strongly recommended Dependent on operating
system and database creation options; maximum of 20,000
x (database block size) |
Database files |
Maximum per tablespace Maximum per database |
Operating system
dependent; usually 1022 65533. May be less on
some operating systems Limited also by size of database blocks and by
the DB_FILES initialization parameter for a particular instance |
Database extents |
Maximum |
2 GB, regardless of the
maximum file size allowed by the operating system |
Database file size |
Maximum |
Operating system
dependent. Limited by maximum operating system file size; typically 222
or 4M blocks |
MAXEXTENTS |
Default value Maximum |
Derived from tablespace
default storage or DB_BLOCK_SIZE initialization parameter Unlimited |
Redo Log Files |
Maximum number of
logfiles Maximum number of
logfiles per group |
Limited by value of
MAXLOGFILES parameter in the CREATE DATABASE statement. Control file
can be resized to allow more entries; ultimately an operating system
limit Unlimited |
Redo Log File Size |
Minimum size Maximum size |
50 KB Operating system limit;
typically 2 GB |
Tablespaces
|
Maximum number per
database |
64 KB Number of
tablespaces cannot exceed the number of database files, as each
tablespace must include at least one file |
|
|
|
|
|
|
|
|
|
|
|
|
Logical Database Limits
GROUP BY clause |
Maximum length |
The GROUP BY expression
and all of the nondistinct aggregates functions (for example, SUM, AVG)
must fit within a single database block. |
Indexes |
Maximum per table total size of indexed
column |
Unlimited
75% of the database
block size minus some overhead |
Columns |
Per table Per index (or clustered
index) |
1000 columns maximum 32 columns maximum |
|
Per bitmapped index |
30 columns maximum |
Constraints
|
Maximum per column |
Unlimited |
Subqueries
|
Maximum levels of
subqueries in a SQL statement |
Unlimited in the FROM
clause of the top-level query; 255 subqueries in the
WHERE clause |
Partitions
|
Maximum length of linear
partitioning key Maximum number of
columns in partition key Maximum number of
partitions allowed per table or index |
4 KB - overhead 16 columns 64 K-1 partitions |
Rollback Segments |
Maximum number per
database |
No limit; limited within
a session by MAX_ROLLBACK_SEGMENTS
initialization parameter |
Rows
|
Maximum number per table |
Unlimited |
SQL Statement Length |
Maximum length of
statements |
64 KB 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. The limits typically range from 2000 to 3000
lines of code. See Also: Your PL/SQL or Developer/2000 documentation
for details |
Trigger Cascade Limit |
Maximum value |
Operating
system-dependent, typically 32 |
Users and Roles |
Maximum |
2,147,483,638 |
Tables
|
Maximum per clustered
table Maximum per database
|
32 tables Unlimited |
|
|
|
Process and Runtime
Limits
Instances per database |
Maximum number of
cluster database instances per database |
Operating
system-dependent |
Locks
|
Row-level
Distributed Lock Manager |
Unlimited Operating system
dependent |
SGA size
|
Maximum value |
Operating
system-dependent; typically 2 to 4 GB for 32-bit operating systems, and
> 4 GB for 64-bit operating systems |
Advanced Queuing
Processes |
Maximum per instance |
10 |
Job Queue Processes |
Maximum per instance |
1000 |
I/O Slave Processes |
Maximum per background
process (DBWR, LGWR, etc.) Maximum per Backup
session |
15 15 |
Sessions
|
Maximum per instance |
32K; limited by
PROCESSES and SESSIONS initialization parameters |
Global Cache Service
Processes |
Maximum per instance |
10 |
Shared Servers |
Maximum per instance |
Unlimited within
constraints set by PROCESSES and SESSIONS initialization parameters,
for instance |
Dispatchers |
Maximum per instance |
Unlimited within
constraints set by PROCESSES and SESSIONS initialization parameters,
for instance |
Parallel Execution
Slaves |
Maximum per instance |
Unlimited within
constraints set by PROCESSES and SESSIONS initialization parameters,
for instance |
Backup Sessions |
Maximum per instance |
Unlimited within
constraints set by PROCESSES and SESSIONS initialization parameters,
for instance |
Oracle 8i Limits
Datatype Limits
Datatypes |
Limit |
Comments |
BFILE |
maximum size: 4GB maximum size of file
name: 255 characters maximum size of open
BFILEs: see comments
|
The maximum number of
BFILEs is limited by SESSION_MAX_OPEN_FILES, which is itself limited by
the maximum number of open files the operating
system will allow. |
BLOB |
4GB 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
|
4GB 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
(2GB) maximum |
Only one LONG column
allowed per table |
NCHAR |
2000 bytes |
|
NCHAR VARYING |
4000 bytes |
|
NCLOB |
4GB 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 to
power of 25 maximum value -999...(38 9's)x 10 to
power of 125 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 |
|
Physical Database Limits
Item
|
Type of Limit |
Limit Value |
Database Block Size |
minimum maximum |
2048 bytes; must be a
multiple of O/S
physical block size O/S-dependent never more
than 32KB |
Database Blocks |
minimum in initial
extent of a segment maximum per datafile |
2 blocks platform dependent; typically 2 to power of 22 blocks |
Controlfiles |
number of controlfiles |
1 minimum: 2 or more (on
separate devices) strongly recommended |
|
size of controlfile |
dependent on O/S and
database creation options; maximum of 20,000 x (database block size) |
Database files |
maximum per tablespace maximum per database |
O/S dependent, usually
1022 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 to power of 22 or 4M blocks |
MAXEXTENTS |
default value maximum |
derived from tablespace
default storage or DB_BLOCK_SIZE unlimited |
Redo Log Files |
maximum number of logfiles maximum number of
logfiles per group |
LOG_FILES initialization
parameter, or
MAXLOGFILES in CREATE
DATABASE; controlfile can be resized to
allow more entries; ultimately an O/S limit Unlimited |
Redo Log File Size |
minimum size maximum size |
50K bytes 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. |
Logical Database Limits
Item |
Type |
Limit |
GROUP BY clause |
maximum length |
The group-by expression
and all of the non-distinct aggregate
(e.g., sum, avg) need to fit within a
single database block. |
Indexes |
maximum per table total size of indexed
column
|
Unlimited
40% of the database
block size minus some overhead. |
Columns |
table
indexed (or clustered
index) bitmapped index |
1000 columns maximum 32 columns maximum 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 rocedures 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 maximum per database |
32 tables
unlimited |
Schema Object Naming
Item |
Limit
|
Names of databases
|
8 bytes |
Names of database links
|
128 bytes |
All other schema objects
|
30 bytes |
Process / Runtime Limits
Item
|
Type
|
Limit |
Instances per database |
maximum number of OPS
instances per database |
O/S dependent |
Locks |
row-level
Distributed Lock Manager |
unlimited
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
|