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

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.

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 January 1, 4712 BC to December 31, 9999 AD

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

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

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

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

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

2³¹-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

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.

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

Item

Limit                       

Names of databases                 

8 bytes

Names of database links            

128 bytes

All other schema objects           

30 bytes

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