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 |
