Oracle 中各种limit总结
Datatype LimitsDatatypes Limit Comments BFILEMaximum 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 ofBFILEs 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) 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)
Precision38 significant digitsNoneRAWMaximum size: 2000 bytesNoneVARCHARMaximum size: 4000 bytesNoneVARCHAR2Maximum size: 4000 bytesNonePhysical Database LimitsItem Type of Limit Limit Value Database 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 222 - 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 database65533May 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 222 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 statementControl 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 KNumber 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 ( 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) 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 (222) blocks.External Tables fileMaximum sizeDependent on the operating system.An external table can be composed of multiple files.
Logical Database LimitsItem Type of Limit Limit Value GROUP 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 LimitsItem Type of Limit Limit Value Instances 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 instance231; 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
DB_FILESinitialization parameter for a particular instanceGROUP 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 query255 subqueries in the WHERE clause2 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 instance231; 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