Restrictions on ORACLE databases and ORACLE databases

Restrictions on ORACLE databases and ORACLE databases

How many tablespaces can an ORACLE database have at most )? How many Database files can a Database have and how many data files can a Database have? When encountering these problems, you can only query official documents, and your memory is limited. The so-called good memory is not as bad as a pen. The following are some restrictions on ORACLE 10 Gb database for reference.

 

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048Bytes; must be a multiple of operating system physical block size
Maximum	Operating system dependent; never more32KB
Database Blocks	Minimum in initial extent of a segment.	2Blocks
Maximum per datafile	Platform dependent; typically 222-1 blocks
Controlfiles	Number of control files	1Minimum;2Or more (on separate devices) stronugly recommended
Size of a control file	Dependent on operating system and database creation options; maximum20,000X (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually1022
Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and byDB_FILESInitialization parameter for a participant instance
Database extents	Maximum per dictionary managed tablespace	4GB * physical block size (with K/M modifier );4GB (without K/M modifier)
Maximum per locally managed (uniform) tablespace	2GB * physical block size (with K/M modifier );2GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or4MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage orDB_BLOCK_SIZEInitialization parameter
Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by valueMAXLOGFILESParameter inCREATE DATABASEStatement Control file can be resized to allow more entries; ultimately an operating system limit
Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	50 KB
Maximum size	Operating system limit; typically2GB
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	Limit
CREATE MATERIALIZED VIEWDefinition	Maximum size	64 K Bytes
GROUPClause	Maximum length	TheGROUPExpression and all of the nondistinct aggregate functions (for example,SUM,AVG) Must fit within a single database block.
Indexes	Maximum per table	Unlimited
Total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000Columns maximum
Per index (or clustered index)	32Columns maximum
Per bitmapped index	30Columns maximum
Constraints	Maximum per column	Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement	Unlimited inFROMClause of the top-level query 255Subqueries inWHEREClause
Partitions	Maximum length of linear partitioning key	4KB-overhead
Maximum number of columns in partition key	16Columns
Maximum number of partitions allowed per table or index	1024 K-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 from2000To3000Lines of code. See Also:Your PL/SQL or Developer/2000 documentation for details
Trigger Cascade Limit	Maximum value	Operating system-dependent, typically32
Users and Roles	Maximum	2,147,483,638
Tables	Maximum per clustered table	32Tables
Maximum per database	Unlimited

References:

Http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits002.htm#i287915
Https://docs.oracle.com/cd/B19306_01/server.102/b14237/limits003.htm#i288032