Oracle Database Limits

Oracle 8.0.x and 8.1.x Database Limits Datatype Limits Datatypes BFILE

BLOB

Limit maximum size: 4GB maximum size of file name: 255 characters maximum size of open BFILEs: see comments 4GB maximum

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. The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)

CHAR CHAR VARYING CLOB

2000 bytes maximum 4000 bytes 4GB maximum

Literals (characters or numbers in SQL or PL/SQL LONG

4000 characters maximum

NCHAR NCHAR VARYING NCLOB

2000 bytes 4000 bytes 4GB maximum

NUMBER

999...(38 9's)x10 to power of 25 maximum value -999...(38 9's)x 10 to power of 125 minimum value 38 significant digits 2000 bytes maximum 4000 bytes maximum 4000 bytes maximum

The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)

231-1 bytes (2GB) maximum

Precision RAW VARCHAR VARCHAR2

Only one LONG column allowed per table The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000) Can be represented to full 38-digit precision (the mantissa).

Physical Database Limits Item Database Block Size Database Blocks Controlfiles

Type of Limit minimum maximum minimum in initial extent of a segment maximum per datafile number of controlfiles size of controlfile

Database files

maximum per tablespace maximum per database

Limit Value 2048 bytes; must be a multiple of O/S physical block size O/S-dependent never more than 32KB 2 blocks platform dependent; typically 2 to power of 22 blocks 1 minimum: 2 or more (on separate devices) strongly recommended dependent on O/S and database creation options; maximum of 20,000 x (database block size) O/S dependent, usually 1022 65533; may be less on some operating systems; limited also by size of database

Database file size

maximum

MAXEXTENTS

default value

Redo Log Files

maximum maximum number of

blocks, and by the DB_FILES init parameter for a particular instance O/S dependent, limited by maximum O/S file size; typically 2 to power of 22 or 4M blocks derived from tablespace default storage or DB_BLOCK_SIZE logfiles

unlimited 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 Tablespaces

maximum number of logfiles per group minimum size maximum size maximum number per database

50K bytes O/S limit, typically 2GB 64K ; Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file.

Logical Database Limits Item GROUP BY clause

Type maximum length

Indexes

maximum per table total size of indexed column

Columns

Rollback Segments

table indexed (or clustered index) bitmapped index maximum per column maximum number maximum length of linear partitioning key maximum number of columns in partition key maximum number of partitions allowed per table or index maximum number per database

Rows SQL Statement Length

maximum number per table maximum length of statements

Stored Packages

maximum size

Constraints Nested Queries Partitions

Limit The group-by expression and all of the non-distinct aggregate (e.g., sum, avg) need to fit within a single database block. Unlimited 40% of the database block size minus some overhead. 1000 columns maximum 32 columns maximum 30 columns maximum Unlimited 255 4KB - overhead 16 columns 64K-1 partitions no limit; limited within a session by MAX_ROLLBACK_SEGMENTS init parameter no limit 64K maximum; particular tools may impose lower limits PL/SQL and Developer/2000 may

Trigger Cascade Limit Users and Roles Tables

maximum value maximum maximum per clustered table maximum per database

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 20003000 lines of code. O/S dependent, typically 32 2,147,483,638 32 tables unlimited

Schema Object Naming Item Names of databases Names of database links All other schema objects

Limit 8 bytes 128 bytes 30 bytes

Process / Runtime Limits Item Instances per database Locks SGA size

Type maximum number of OPS instances per database row-level Distributed Lock Manager maximum value

Limit O/S dependent unlimited O/S dependent O/S dependent, typically 2-4 GB for 32bit O/S, > 4 GB for 64 bit O/S

Oracle8i ONLY Advanced Queuing Processes

maximum per instance

10

maximum per instance maximum per background process (DBWR, LGWR, etc.)

36 15

Sessions

maximum per Backup session maximum per instance

LCK Processes MTS Servers

maximum per instance maximum per instance

Dispatchers

maximum per instance

Parallel Execution Slaves

maximum per instance

Backup Sessions

maximum per instance

15 32K, limited by PROCESSES and SESSIONS init parameters 10 Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance. Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance. Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.

Oracle8i ONLY Job Queue Processes I/O Slave Processes

9i Database Limits Datatype Limits VARCHAR2 NVARCHAR2 NUMBER( p,s) LONG DATE TIMESTAMP( fractional_seconds_precision)

TIMESTAMP( fractional_seconds_precision) WITH TIME ZONE TIMESTAMP( fractional_seconds_precision)WITH LOCAL TIME ZONE INTERVAL YEAR( year_precision) TO MONTH INTERVAL DAY (day_precision) TO SECOND ( fractional_seconds_precision)

RAW( size) LONG RAW ROWID UROWID [( size)]

CHAR( size)[BYTE |CHAR] NCHAR( size)

CLOB NCLOB BLOB BFILE

Maximum size is 4000 Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes The precision p can range from 1 to 38. The scale s can range from -84 to 127 up to 2 gigabytes, or 231 -1 bytes range from January 1, 4712 BC to December 31, 9999 AD 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 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 same as TIMESTAMP( fractional_seconds_precision) WITH TIME ZONE year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2 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 Maximum size is 2000 bytes up to 2 gigabytes Base 64 string representing the unique address of a row in its table 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 Fixed-length character data of length size bytes.Maximum size is 2000 bytes. Default and minimum size is 1 byte 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 Maximum size is 4 gigabytes Maximum size is 4 gigabytes Maximum size is 4 gigabytes Maximum size is 4 gigabytes

Physical Database Limits Database Block Size

Database Blocks

Controlfiles

Minimum

2048 bytes; must be a multiple of operating system physical block size

Maximum

Operating system dependent; never more than 32 KB 2 blocks

Minimum in initial extent of a segment Maximum per datafile Number of control files Size of a control file

Database files

Maximum per tablespace Maximum per database

Database extents

Maximum

Database file size

Maximum

MAXEXTENTS

Default value

Redo Log Files

Maximum Maximum number of logfiles

Redo Log File Size

Maximum number of logfiles per group Minimum size Maximum size Maximum number per database

Tablespaces

Platform dependent; typically 222-1 blocks 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) 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 2 GB, regardless of the maximum file size allowed by the operating system Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M blocks Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter Unlimited 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 50 KB Operating system limit; typically 2 GB 64 KB Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file

Logical Database Limits GROUP BY clause

Maximum length

Indexes

Maximum per table total size of indexed column

Columns

Constraints Subqueries Partitions

Rollback Segments

Per table Per index (or clustered index) Per bitmapped index Maximum per column

The GROUP BY expression and all of the nondistinct aggregates functions (for example, SUM, AVG) must fit within a single database block. Unlimited 75% of the database block size minus some overhead 1000 columns maximum 32 columns maximum 30 columns maximum Unlimited

Maximum levels of subqueries in a SQL statement Maximum length of linear partitioning key

Unlimited in the FROM clause of the top-level query; 255 subqueries in the WHERE clause 4 KB - overhead

Maximum number of columns in partition key

16 columns

Maximum number of partitions allowed per table or index Maximum number per database

64 K-1 partitions No limit; limited within a session by MAX_ROLLBACK_SEGMENTS initialization parameter Unlimited

Rows

Maximum number per table

SQL Statement Length Stored Packages

Maximum length of statements Maximum size

Trigger Cascade Limit

Maximum value

64 KB maximum; particular tools may impose lower limits 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 Operating system-dependent, typically 32

Users and Roles

Maximum

2,147,483,638

Tables

Maximum per clustered table Maximum per database

32 tables Unlimited

Process and Runtime Limits Instances per database Locks

Maximum number of cluster database instances per database Row-level

Operating system-dependent Unlimited

SGA size

Distributed Lock Manager Maximum value

Operating system dependent Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems 10 1000

Advanced Queuing Processes Job Queue Processes

Maximum per instance Maximum per instance

I/O Slave Processes

Maximum per background process (DBWR, LGWR, etc.)

15

Sessions

Maximum per Backup session Maximum per instance

Global Cache Service Processes

Maximum per instance

15 32K; limited by PROCESSES and SESSIONS initialization parameters 10

Shared Servers

Maximum per instance

Dispatchers

Maximum per instance

Parallel Execution Slaves

Maximum per instance

Backup Sessions

Maximum per instance

Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance