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

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

Advanced Queuing Processes

maximum per instance

10

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