For those who find it difficult to find in Reference Manuals
Physical Database Limits
Code:
Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes;
must be a multiple of operating system
physical block size
Maximum Operating system dependent;
never more than 32 KB
Database Blocks Minimum in initial 2 blocks
extent of
a segment.
Maximum per datafile Platform dependent;
typically 222-1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly
recommended
Size of a control file Dependent on operating system and database creation
options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system 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 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 Derived from tablespace default storage or DB_BLOCK_
SIZE initialization parameter
Maximum Unlimited
Redo Log Files Maximum number of Limited by value of LOG_FILES initialization parameter
logfiles or MAXLOGFILES parameter in the CREATE DATABASE
statement
Control file can be resized to allow more entries;
ultimately an operating system limit
Maximum number of Unlimited
logfiles per group
Redo Log File Size Minimum size 50 KB
Maximum size Operating system limit; typically 2 GB
Tablespaces Maximum number per 64 K
database Number of tablespaces cannot exceed the number of
database files, as each tablespace must include at least
one file
Logical Database Limits
Code:
Item Type of Limit Limit Value
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 Unlimited
total size of indexed column 75% of the database block size minus some
overhead
Columns Per table 1000 columns maximum
Per index (or clustered index) 32 columns maximum
Per bitmapped index 30 columns maximum
Constraints Maximum per column Unlimited
Subqueries Maximum levels of subqueries Unlimited in the FROM clause of the top-level
in a SQL statement query
255 subqueries in the WHERE clause
Partitions Maximum length of linear 4 KB - overhead
partitioning key
Maximum number of columns 16 columns
in partition key
Maximum number of partitions 64 K-1 partitions
allowed per table or index
Rollback Segments Maximum number per database No limit; limited within a session by the MAX_
ROLLBACK_SEGMENTS initialization parameter
Rows Maximum number per table Unlimited
SQL Statement Length Maximum length of statements 64 K 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 32 tables
Maximum per database Unlimited
Process and Runtime Limits
Code:
Item Type of Limit Limit Value
Instances per database Maximum number of OPS Operating system-dependent
instances per database
Locks Row-level Unlimited
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 Maximum per instance 10
Processes
Job Queue Processes Maximum per instance 1000
I/O Slave Processes Maximum per background 15
process (DBWR, LGWR, etc.)
Maximum per Backup session 15
Sessions Maximum per instance 32K; limited by thePROCESSESandSESSIONS
initialization parameters
LCK Processes Maximum per instance 10
Shared Servers Maximum per instance Unlimited within constraints set by the
PROCESSES and SESSIONS initialization
parameters, for instance
Dispatchers Maximum per instance Unlimited within constraints set byPROCESSES
and SESSIONS initialization parameters, for
instance
Parallel Maximum per instance Unlimited within constraints set byPROCESSES
Execution Slaves and SESSIONS initialization parameters, for
instance
Backup Sessions Maximum per instance Unlimited within constraints set byPROCESSES
and SESSIONS initialization parameters, for
instance
HTH
Regards