代码改变世界

ORACLE数据库的限制

  潇湘隐者  阅读(1809)  评论(0编辑  收藏  举报

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

编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2014-08-19 [翻译] 聚集索引表 VS 堆表
2010-08-19 清除Chrome浏览器的历史记录、缓存
点击右上角即可分享
微信分享提示