Oracle Database
Limits in Oracle 11g Release 2 (11.2,
11gR2)
This article lists the limits of values associated with Oracle
database functions and objects on several levels. There is usually a hard-coded
limit that cannot be exceeded. This value may be further restricted for any
given operating system.
Database limits are divided into four categories:
·
Datatype
Limits
Datatypes
|
Limit
|
Comments
|
BFILE
|
Maximum size: 4 GB
Maximum size of a file
name: 255 characters
Maximum size of a
directory name: 30 characters
Maximum number of open
BFILEs: see Comments
|
The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is
itself limited by the maximum number of open files the operating system will
allow.
|
BLOB
|
Maximum size: (4 GB -
1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)
|
The number of LOB
columns per table is limited only by the maximum number of columns per table
(that is, 1000).
|
CHAR
|
Maximum size: 2000
bytes
|
None
|
CHAR VARYING
|
Maximum size: 4000
bytes
|
None
|
CLOB
|
Maximum size: (4 GB -
1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)
|
The number of LOB
columns per table is limited only by the maximum number of columns per table
(that is, 1000).
|
Literals (characters
or numbers in SQL or PL/SQL)
|
Maximum size: 4000
characters
|
None
|
LONG
|
Maximum size: 2 GB - 1
|
Only one LONG column is allowed per table.
|
NCHAR
|
Maximum size: 2000
bytes
|
None
|
NCHAR VARYING
|
Maximum size: 4000
bytes
|
None
|
NCLOB
|
Maximum size: (4 GB -
1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)
|
The number of LOB
columns per table is limited only by the maximum number of columns per table
(that is, 1000).
|
NUMBER
|
999...(38 9's) x10125 maximum
value
-999...(38 9's) x10125 minimum
value
|
Can be represented to
full 38-digit precision (the mantissa)
Can be represented to
full 38-digit precision (the mantissa)
|
Precision
|
38 significant digits
|
None
|
RAW
|
Maximum size: 2000
bytes
|
None
|
VARCHAR
|
Maximum size: 4000
bytes
|
None
|
VARCHAR2
|
Maximum size: 4000
bytes
|
None
|
·
Physical
Database Limits
Item
|
Type of Limit
|
Limit Value
|
Database Block Size
|
Minimum
|
2048 bytes; must be a multiple of operating system physical
block size
|
Database Block Size
|
Maximum
|
Operating system
dependent; never more than 32 KB
|
Database Blocks
|
Minimum in initial
extent of a segment
|
2 blocks
|
Database Blocks
|
Maximum per datafile
|
Platform dependent;
typically 222 - 1 blocks
|
Controlfiles
|
Number of control
files
|
1 minimum; 2 or more (on
separate devices) strongly recommended
|
Controlfiles
|
Size of a control file
|
Dependent on operating
system and database creation options; maximum of20,000 x (database block size)
|
Database files
|
Maximum per tablespace
|
Operating system
dependent; usually 1022
|
Database files
|
Maximum per database
|
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 per dictionary
managed tablespace
|
4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
|
Database extents
|
Maximum per locally
managed (uniform) tablespace
|
2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
|
Database file size
|
Maximum
|
Operating system
dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
|
MAXEXTENTS
|
Default value
|
Derived from
tablespace default storage or DB_BLOCK_SIZE initialization
parameter
|
MAXEXTENTS
|
Maximum
|
Unlimited
|
Redo Log Files
|
Maximum number of
logfiles
|
Limited by value of MAXLOGFILES parameter in the CREATE DATABASEstatement
Control file can be
resized to allow more entries; ultimately an operating system limit
|
Redo Log Files
|
Maximum number of
logfiles per group
|
Unlimited
|
Redo Log File Size
|
Minimum size
|
4 MB
|
Redo Log File Size
|
Maximum Size
|
Operating system
limit; typically 2 GB
|
Tablespaces
|
Maximum number per
database
|
64 K
Number of tablespaces
cannot exceed the number of database files because each tablespace must
include at least one file
|
Bigfile Tablespaces
|
Number of blocks
|
A bigfile tablespace
contains only one datafile or tempfile, which can contain up to approximately
4 billion ( 232 ) blocks. The maximum size of the single
datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks
and 32 TB for a tablespace with 8 K blocks.
|
Smallfile
(traditional) Tablespaces
|
Number of blocks
|
A smallfile tablespace
is a traditional Oracle tablespace, which can contain 1022 datafiles or
tempfiles, each of which can contain up to approximately 4 million (222)
blocks.
|
External Tables file
|
Maximum size
|
Dependent on the
operating system.
An external table can
be composed of multiple files.
|
·
Logical
Database Limits
Item
|
Type of Limit
|
Limit Value
|
GROUP BY clause
|
Maximum length
|
The GROUP BY expression and all of the nondistinct aggregate functions
(for example, SUM, AVG) must fit within a
single database block.
|
Indexes
|
Maximum per table
|
Unlimited
|
Indexes
|
Total size of indexed
column
|
75% of the database
block size minus some overhead
|
Columns
|
Per table
|
1000 columns maximum
|
Columns
|
Per index (or
clustered index)
|
32 columns maximum
|
Columns
|
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
|
4 KB - overhead
|
Partitions
|
Maximum number of
columns in partition key
|
16 columns
|
Partitions
|
Maximum number of
partitions allowed per table or index
|
1024K - 1
|
Rows
|
Maximum number per
table
|
Unlimited
|
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 3000lines of code.
|
Trigger Cascade Limit
|
Maximum value
|
Operating
system-dependent, typically 32
|
Users and Roles
|
Maximum
|
2,147,483,638
|
Tables
|
Maximum per clustered
table
|
32 tables
|
Tables
|
Maximum per database
|
Unlimited
|
·
Process
and Runtime Limits
Item
|
Type of Limit
|
Limit Value
|
Instances per database
|
Maximum number of
cluster database instances per database
|
Operating
system-dependent
|
Locks
|
Row-level
|
Unlimited
|
Locks
|
Distributed Lock
Manager
|
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.)
|
15
|
I/O Slave Processes
|
Maximum per Backup
session
|
15
|
Sessions
|
Maximum per instance
|
32 KB; limited by the PROCESSES and SESSIONS initialization parameters
|
Global Cache Service
Processes
|
Maximum per instance
|
10
|
Shared Servers
|
Maximum per instance
|
Unlimited within
constraints set by the PROCESSES and SESSIONSinitialization
parameters, for instance
|
Dispatchers
|
Maximum per instance
|
Unlimited within
constraints set by PROCESSES and SESSIONSinitialization
parameters, for instance
|
Parallel Execution
Slaves
|
Maximum per instance
|
Unlimited within
constraints set by PROCESSES and SESSIONSinitialization
parameters, for instance
|
Backup Sessions
|
Maximum per instance
|
Unlimited within
constraints set by PROCESSES and SESSIONSinitialization
parameters, for instance
|
No comments:
Post a Comment