miércoles, 17 de enero de 2018

Oracle Limits

Logical Database Limits

ItemType of LimitLimit Value
IndexesMaximum per tableUnlimited
IndexesTotal size of indexed column75% of the database block size minus some overhead
ColumnsPer table1000 columns maximum
ColumnsPer index (or clustered index)32 columns maximum
ColumnsPer bitmapped index30 columns maximum
ConstraintsMaximum per columnUnlimited
SubqueriesMaximum 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 - overhead
PartitionsMaximum number of columns in partition key16 columns
PartitionsMaximum number of partitions allowed per table or index1024K - 1
SubpartitionsMaximum number of subpartitions in a composite partitioned table1024K - 1
RowsMaximum number per tableUnlimited
System Change Numbers (SCNs)Maximum281,474,976,710,656, which is 281 trillion SCNs
Stored PackagesMaximum sizeApproximately 6,000,000 lines of code.
Trigger Cascade LimitMaximum valueOperating system-dependent, typically 32
Users and RolesMaximum2,147,483,638
TablesMaximum per clustered table32 tables
TablesMaximum per databaseUnlimited
A.2 Physical Database Limits
ItemType of LimitLimit Value
Database Block SizeMinimum2048 bytes; must be a multiple of operating system physical block size
Database Block SizeMaximumOperating system dependent; never more than 32 KB
Database BlocksMinimum in initial extent of a segment2 blocks
Database BlocksMaximum per datafilePlatform dependent; typically 222 - 1 blocks
ControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommended
ControlfilesSize of a control fileMaximum of 201031680 logical blocks
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533
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 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. See the Bigfile Tablespaces and Smallfile (traditional) Tablespaces rows for more information about the maximum database file size in these types of tablespaces.
MAXEXTENTSDefault valueDerived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTSMaximumUnlimited
Redo 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 groupUnlimited
Redo Log File SizeMinimum size4 MB
Redo Log File SizeMaximum SizeOperating system limit; typically 2 GB
TablespacesMaximum 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 ( 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.