Datatype Limits
Datatypes |
Limit |
Comments |
BFILE |
Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments |
The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow. |
BLOB |
Maximum 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 maximum number of columns per table (that is, 1000Foot 1 ). |
CHAR |
Maximum size: 2000 bytes |
None |
CHAR VARYING |
Maximum size: 4000 bytes |
None |
CLOB |
Maximum 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 maximum number of columns per table (that is, 1000Footref 1). |
Literals (characters or numbers in SQL or PL/SQL) |
Maximum size: 4000 characters |
None |
LONG |
Maximum size: 2 GB - 1 |
Only one LONG column is allowed per table. |
NCHAR |
Maximum size: 2000 bytes |
None |
NCHAR VARYING |
Maximum size: 4000 bytes |
None |
NCLOB |
Maximum 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 maximum number of columns per table (that is, 1000Footref 1). |
NUMBER |
999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum 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 |
Maximum size: 2000 bytes |
None |
VARCHAR |
Maximum size: 4000 bytes |
None |
VARCHAR2 |
Maximum size: 4000 bytes |
None |
Physical Database Limits
Item |
Type of Limit |
Limit Value |
Database Block Size |
Minimum |
2048 bytes; must be a multiple of operating system physical block size |
Database Block Size |
Maximum |
Operating system dependent; never more than 32 KB |
Database Blocks |
Minimum in initial extent of a segment |
2 blocks |
Database Blocks |
Maximum per datafile |
Platform dependent; typically 222 - 1 blocks |
Controlfiles |
Number of control files |
1 minimum; 2 or more (on separate devices) strongly recommended |
Controlfiles |
Size of a control file |
Dependent on operating system and database creation options; maximum of 25,000 x (database block size) |
Database files |
Maximum per tablespace |
Operating system dependent; usually 1022 |
Database files |
Maximum per database |
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 per dictionary managed tablespace |
4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
Database extents |
Maximum per locally managed (uniform) tablespace |
2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
Database file size |
Maximum |
Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks |
MAXEXTENTS |
Default value |
Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
MAXEXTENTS |
Maximum |
Unlimited |
Redo Log Files |
Maximum number of logfiles |
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 |
Redo Log Files |
Maximum number of logfiles per group |
Unlimited |
Redo Log File Size |
Minimum size |
4 MB |
Redo Log File Size |
Maximum Size |
Operating system limit; typically 2 GB |
Tablespaces |
Maximum number per database |
64 K Number of tablespaces cannot exceed the number of database 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 maximum 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 |
Maximum size |
Dependent on the operating system. An external table can be composed of multiple files. |
Logical Database Limits
Item |
Type of Limit |
Limit Value |
GROUP BY clause |
Maximum length |
The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block. |
Indexes |
Maximum per table |
Unlimited |
Indexes |
Total size of indexed column |
75% of the database block size minus some overhead |
Columns |
Per table |
1000 columns maximum |
Columns |
Per index (or clustered index) |
32 columns maximum |
Columns |
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 |
4 KB - overhead |
Partitions |
Maximum number of columns in partition key |
16 columns |
Partitions |
Maximum number of partitions allowed per table or index |
1024K - 1 |
Rows |
Maximum number per table |
Unlimited |
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 |
32 tables |
Tables |
Maximum per database |
Unlimited |
Process and Runtime Limits
Item |
Type of Limit |
Limit Value |
Instances per database |
Maximum number of cluster database instances per database |
Operating system-dependent |
Locks |
Row-level |
Unlimited |
Locks |
Distributed Lock Manager |
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.) |
15 |
I/O Slave Processes |
Maximum per Backup session |
15 |
Sessions |
Maximum per instance |
231; limited by the PROCESSES and SESSIONS initialization parameters |
Global Cache Service Processes |
Maximum per instance |
10 |
Shared Servers |
Maximum per instance |
Unlimited within constraints set by the 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 |
Services |
Maximum per instance |
115 |
'[ORACLE] > Admin' 카테고리의 다른 글
oracle 11g deferred_segment_created (0) | 2012.01.13 |
---|