ORACLE數據庫最多可以擁有多少個表空間(Tablespace)?數據庫最多擁有多少個數據文件(Database files)、數據庫的數據文件最大可以多大?遇到這些問題只能查詢官方文檔,人的記憶能力是有限的。正所謂好記性不如爛筆頭。下面是ORACLE 10g數據庫的一些限制,以供參考。
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
Maximum
Operating system dependent; never more than 32 KB
Database Blocks
Minimum in initial extent of a segment.
2 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
Size of a control file
Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files
Maximum per tablespace
Operating system dependent; usually 1022
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)
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
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
Maximum number of logfiles per group
Unlimited
Redo Log File Size
Minimum size
50 KB
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 32K blocks and 32TB for a tablespace with 8K 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
64K Bytes
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
total size of indexed column
75% of the database block size minus some overhead
Columns
Per table
1000 columns maximum
Per index (or clustered index)
32 columns maximum
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
Maximum number of columns in partition key
16 columns
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
Maximum per database
Unlimited
參考資料:
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