Oracle 9i Database Limits

 

Schema Object Naming

 

Item

Limit                       

Names of databases                 

8 bytes

Names of database links            

128 bytes

All other schema objects           

30 bytes

  


Process / Runtime Limits

 

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                



Datatype Limits

 

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

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

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

NCLOB

Maximum size is 4 gigabytes.

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

BLOB

Maximum size is 4 gigabytes

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

BFILE

Maximum size is 4 gigabytes

Maximum size of file name: 255 characters  

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

 

 

 

Physical Database Limits

 

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

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

 


Logical Database Limits

 

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)

Per bitmapped index

1000 columns maximum

32 columns maximum
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

 


Process and Runtime Limits

 

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