Oracle8 Reference
Release 8.0

A58242-01

Library

Product

Contents

Index

Prev Next

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.

 




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index