Restrictions to Consider when Creating Tables

Here are some restrictions that may affect your table planning and usage:

• Tables containing object types cannot be imported into a pre-Oracle8 DB.
• You cannot merge an exported table into a preexisting table having the same name in a different schema.
• You cannot move types and extent tables to a different schema when the original data still exists in the DB.
• Oracle DB has a limit on the total number of columns that a table (or attributes that an object type) can have.

Further, when you create a table that contains user-defined type data, the DB maps columns of user-defined type to relational columns for storing the user-defined type data. This causes additional relational columns to be created. This results in "hidden" relational columns that are not visible in a DESCRIBE table statement and are not returned by a SELECT * statement. Therefore, when you create an object table, or a relational table with columns of REF, varray, nested table, or object type, be aware that the total number of columns that the DB actually creates for the table can be more than those you specify.

Database Limits

The limits of values associated with DB functions and objects. Limits exist on several levels in the DB. There is usually a hard-coded limit in the DB that cannot be exceeded. This value may be further restricted for any given OS. DB limits are divided into four categories:

• Datatype Limits
• Physical DB Limits
• Logical DB Limits
• Process and Runtime Limits

 

Datatype Limits

Datatypes	Limit	Comments
BFILE	Max size: 4 GB Max size of a file name: 255 characters Max size of a directory name: 30 characters Max number of open BFILEs: see Comments	Max number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the max number of open files the OS will allow.
BLOB	Max size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the max number of columns per table (that is, 1000 note1).
CHAR	Max size: 2000 bytes	None
CHAR VARYING	Max size: 4000 bytes	None
CLOB	Max size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the max number of columns per table (that is, 1000 note1).
Literals (characters or numbers in SQL or PL/SQL)	Max size: 4000 characters	None
LONG	Max size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Max size: 2000 bytes	None
NCHAR VARYING	Max size: 4000 bytes	None
NCLOB	Max size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	Number of LOB columns per table is limited only by the max number of columns per table (that is, 1000 note1).
NUMBER	999...(38 9's) x 10125 max value -999...(38 9's) x10125 min value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Max size: 2000 bytes	None
VARCHAR	Max size: 4000 bytes	None
VARCHAR2	Max size: 4000 bytes	None

note1: The absolute max number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, refer to Oracle DB Administrator's Guide.

Physical DB Limits

Item	Type of limit	Limit value
DB Block Size	Min	2048 bytes; must be a multiple of OS physical block size
DB Block Size	Max	OS dependent; never more than 32 KB
DB Blocks	Min in initial extent of a segment	2 blocks
DB Blocks	Max per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 min; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on OS and DB creation options; max of 25,000 x (DB block size)
DB files	Max per tablespace	OS dependent; usually 1022
DB files	Max per DB	65533. May be less on some OS. Limited also by size of DB blocks and by the DB_FILES initialization parameter for a particular instance
DB extents	Max per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
DB extents	Max per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
DB file size	Max	OS dependent. Limited by max OS file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Max	Unlimited
Redo Log Files	Max number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DB statement. Control file can be resized to allow more entries; ultimately an OS limit
Redo Log Files	Max number of logfiles per group	Unlimited
Redo Log File Size	Min size	4 MB
Redo Log File Size	Max Size	OS limit; typically 2 GB
Tablespaces	Max number per DB	64 K. Number of tablespaces cannot exceed the number of DB files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (232) blocks. The max size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Max size	Dependent on the OS. An external table can be composed of multiple files.

Logical DB Limits

Item	Type of limit	Limit value
GROUP BY clause	Max length	GROUP BY expression and all of the non-distinct aggregate functions (for ex., SUM, AVG) must fit within a single DB block.
Indexes	Max per table	Unlimited
Indexes	Total size of indexed column	75% of the DB block size minus some overhead
Columns	Per table	1000 columns max
Columns	Per index (or clustered index)	32 columns max
Columns	Per bitmapped index	30 columns max
Constraints	Max per column	Unlimited
Subqueries	Max levels of subqueries in a SQL statement	Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause
Partitions	Max length of linear partitioning key	4 KB - overhead
Partitions	Max number of columns in partition key	16 columns
Partitions	Max number of partitions allowed per table or index	1024K - 1
Rows	Max number per table	Unlimited
Stored Packages	Max 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.
Trigger Cascade Limit	Max value	OS-dependent, typically 32
Users and Roles	Max	2,147,483,638
Tables	Max per clustered table	32 tables
Tables	Max per DB	Unlimited

note: The limit on how long a SQL statement can be depends on many factors, including DB configuration, disk space, and memory. When an object instance exists in memory, there is no fixed limit on the number of attributes in the object. But the max total amount of memory consumed by an object instance is 4 GB. When an object instance is inserted into a table; the attributes are exploded into separate columns in the table, and the Oracle 1000-column limit applies.

Process & Runtime Limits

Item	Type of limit	Limit value
Instances per DB	max number of cluster DB instances per DB	OS-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	OS dependent
SGA size	max value	OS-dependent; typically 2 to 4 GB for 32-bit OSs, and > 4 GB for 64-bit OSs
Advanced Queuing Processes	max per instance	10
Job Queue Processes	max per instance	1000
I/O Slave Processes	max per background process (DBWR, LGWR, etc.)	15
I/O Slave Processes	max per Backup session	15
Sessions	max per instance	231; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	max per instance	10
Shared Servers	max per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	max per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	max per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	max per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Services	max per instance	115