【Datafile】Oracle單個datafile大小的限制

TaihangMeng發表於2017-11-03
Oracle

   最近在維護資料庫空間，給tablespace新增datafile的時候，由於達到了資料庫最大datafile的個數，所以不能再新增。而我們的單個datafile的大小為2G，所以我就在想，單個datafile的大小限制到底是多少，我們可以相應擴大單個datafile的大小，以減少datafile的個數。


   在網上參考了很多專業人士的技術文件，也做了相應的測試，現在記錄如下：


1、建立普通tablespace時，報錯

SYS@MTH> create tablespace TEST
  2  datafile '/oradat/MTH/TEST01.dbf' size 2000M
  3  autoextend on next 10M maxsize 50000M
  4  extent management local
  5  segment space management auto;
create tablespace TEST
*
ERROR at line 1:
ORA-03206: maximum file size of (6400000) blocks in AUTOEXTEND clause is out of range


2、Oracle官方相關限制設定

Item

Type of Limit

Limit Value

Database Block Size

Minimum

2048 bytes; must be a multiple of operating system physical block size

Database Block Size

Maximum

Operating system dependent; never more than 32 KB

Database Blocks

Minimum in initial extent of a segment

2 blocks

Database Blocks

Maximum per datafile

Platform dependent; typically 2^22 - 1 blocks

Controlfiles

Number of control files

1 minimum; 2 or more (on separate devices) strongly recommended

Controlfiles

Size of a control file

Dependent on operating system and database creation options; maximum of 25,000 x (database block size)

Database files

Maximum per tablespace

Operating system dependent; usually 1022

Database files

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)

Database extents

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 2^22 or 4 MB blocks

MAXEXTENTS

Default value

Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter

MAXEXTENTS

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

Redo Log Files

Maximum number of logfiles per group

Unlimited

Redo Log File Size

Minimum size

4 MB

Redo Log File Size

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 ( 2^32 ) 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 (2^22) blocks.

External Tables file

Maximum size

Dependent on the operating system.

An external table can be composed of multiple files.


每個datafile最多包含2^22-1個block,存在這個限制是因為Oracle的內部ROWID使用22位2進位制數來儲存不同的block號，所以22位最多代表(2^22-1)個block。


3、根據文件，計算我的資料庫單個檔案的大小限制

SYS@MTH> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192


    單個檔案的最大大小：(2^22-1) × 8K=33554432K=32G

             顯然我指定的50000M超過了最大限制，所以報錯。

    檢視到文件中建議單個datafile的大小設定成8G，既避免datafile過小導致的個數過多，難以維護，又能避免datafile過大，一旦出現問題很難恢復的情況。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30776559/viewspace-2146790/，如需轉載，請註明出處，否則將追究法律責任。

相關文章