Oracle 中各种limit总结

Datatype Limits

DatatypesLimitCommentsBFILEMaximum 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 theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.BLOBMaximum 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, 1000).CHARMaximum size: 2000 bytesNoneCHAR VARYINGMaximum size: 4000 bytesNoneCLOBMaximum 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, 1000).Literals (characters or numbers in SQL or PL/SQL)Maximum size: 4000 charactersNoneLONGMaximum size: 2 GB - 1Only one LONG column is allowed per table.NCHARMaximum size: 2000 bytesNoneNCHAR VARYINGMaximum size: 4000 bytesNoneNCLOBMaximum 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, 1000).NUMBER999...(38 9's) x10¹²⁵ maximum value

-999...(38 9's) x10¹²⁵ minimum value

Can be represented to full 38-digit precision (the mantissa)

Can be represented to full 38-digit precision (the mantissa)

Precision38 significant digitsNoneRAWMaximum size: 2000 bytesNoneVARCHARMaximum size: 4000 bytesNoneVARCHAR2Maximum size: 4000 bytesNone

Physical Database Limits

ItemType of LimitLimit ValueDatabase Block SizeMinimum2048 bytes; must be a multiple of operating system physical block sizeDatabase Block SizeMaximumOperating system dependent; never more than 32 KBDatabase BlocksMinimum in initial extent of a segment2 blocksDatabase BlocksMaximum per datafilePlatform dependent; typically 2²² - 1 blocksControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommendedControlfilesSize of a control fileDependent on operating system and database creation options; maximum of 25,000 x (database block size)Database filesMaximum per tablespaceOperating system dependent; usually 1022Database filesMaximum per database65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILESinitialization parameter for a particular instance

Database extentsMaximum per dictionary managed tablespace4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)Database extentsMaximum per locally managed (uniform) tablespace2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)Database file sizeMaximumOperating system dependent. Limited by maximum operating system file size; typically 2²² or 4 MB blocksMAXEXTENTSDefault valueDerived from tablespace default storage or DB_BLOCK_SIZEinitialization parameterMAXEXTENTSMaximumUnlimitedRedo Log FilesMaximum number of logfilesLimited 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 FilesMaximum number of logfiles per groupUnlimitedRedo Log File SizeMinimum size4 MBRedo Log File SizeMaximum SizeOperating system limit; typically 2 GBTablespacesMaximum number per database64 K

Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file

Bigfile TablespacesNumber of blocksA bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2³² ) 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) TablespacesNumber of blocksA smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2²²) blocks.External Tables fileMaximum sizeDependent on the operating system.

An external table can be composed of multiple files.

Logical Database Limits

ItemType of LimitLimit ValueGROUP BY clauseMaximum lengthThe GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.IndexesMaximum per tableUnlimitedIndexesTotal size of indexed column75% of the database block size minus some overheadColumnsPer table1000 columns maximumColumnsPer index (or clustered index)32 columns maximumColumnsPer bitmapped index30 columns maximumConstraintsMaximum per columnUnlimitedSubqueriesMaximum levels of subqueries in a SQL statementUnlimited in the FROM clause of the top-level query

255 subqueries in the WHERE clause

PartitionsMaximum length of linear partitioning key4 KB - overheadPartitionsMaximum number of columns in partition key16 columnsPartitionsMaximum number of partitions allowed per table or index1024K - 1RowsMaximum number per tableUnlimitedStored PackagesMaximum sizePL/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 LimitMaximum valueOperating system-dependent, typically 32Users and RolesMaximum2,147,483,638TablesMaximum per clustered table32 tablesTablesMaximum per databaseUnlimited

Process and Runtime Limits

ItemType of LimitLimit ValueInstances per databaseMaximum number of cluster database instances per databaseOperating system-dependentLocksRow-levelUnlimitedLocksDistributed Lock ManagerOperating system dependentSGA sizeMaximum valueOperating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systemsAdvanced Queuing ProcessesMaximum per instance10Job Queue ProcessesMaximum per instance1000I/O Slave ProcessesMaximum per background process (DBWR, LGWR, etc.)15I/O Slave ProcessesMaximum per Backup session15SessionsMaximum per instance2³¹; limited by the PROCESSES and SESSIONS initialization parametersGlobal Cache Service ProcessesMaximum per instance10Shared ServersMaximum per instanceUnlimited within constraints set by the PROCESSES andSESSIONS initialization parameters, for instanceDispatchersMaximum per instanceUnlimited within constraints set by PROCESSES andSESSIONS initialization parameters, for instanceParallel Execution SlavesMaximum per instanceUnlimited within constraints set by PROCESSES andSESSIONS initialization parameters, for instanceBackup SessionsMaximum per instanceUnlimited within constraints set by PROCESSES andSESSIONS initialization parameters, for instanceServicesMaximum per instance115