大檔案表空間受作業系統限制無法自動擴充套件

之前只知道BIGFILE TABLESPACE只能有一個資料檔案，對於8K的BLOCK_SIZE，BIGFILE TABLESPACE最大為32TB，對於16K一個的BLOCK_SIZE，BIGFILE TABLESPACE最大為64TB，在我的測試環境有一塊10TB的磁碟分割槽，在上面建立了一個BIGFILE TABLESPACE，作業系統是OEL5.7,資料庫版本是11.2.0.2.0，檔案系統是EXT3，在測試圖片載入時，報ORA-1691表空間無法自動擴充套件錯誤。

ORA-1691: unable to extend lobsegment PIC.SYS_LOB0000218810C00010$$

by 64 in tablespace              PICS

ORA-1691: unable to extend lobsegment PIC.SYS_LOB0000218810C00010$$

by 4096 in tablespace              PICS

ORA-1691: unable to extend lobsegment PICS.SYS_LOB0000218355C00010$$

by 64 in tablespace              PICS

ORA-1691: unable to extend lobsegment PICS.SYS_LOB0000218355C00010$$

by 4096 in tablespace              PICS

       而此時資料檔案大小將近2TB，手動調整資料檔案大小為2TB，報ORA-01237錯誤。

SQL> alter database datafile 13 resize 2T;

alter database datafile 13 resize 2T

*

ERROR at line 1:

ORA-01237: cannot extend datafile 13

ORA-01110: data file 13: '/pics/pics01.dbf'

ORA-27059: could not reduce file size

Linux-x86_64 Error: 27: File too large

Additional information: 2

    上面的錯誤提示很清楚，由於資料檔案大小超過2TB檔案系統限制，導致資料檔案無法自動擴充套件，也就是說BIGFILE TABLESPACE的大小也受檔案系統的限制，下面是ORACLE11gR2的館方文件對BIGFILE TABLESPACE的說明：

Bigfile Tablespaces

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks)

 datafile. Traditional smallfile tablespaces, in contrast, can contain multiple

 datafiles, but the files cannot be as large. The benefits of bigfile tablespaces

 are the following:

•   A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile

 tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number

 of datafiles in an Oracle Database is limited (usually to 64K files). Therefore,

 bigfile tablespaces can significantly enhance the storage capacity of an Oracle

 Database.

•   Bigfile tablespaces can reduce the number of datafiles needed for a database.

 An additional benefit is that the DB_FILES initialization parameter and

MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can

 be adjusted to reduce the amount of SGA space required for datafile information

 and the size of the control file.

•   Bigfile tablespaces simplify database management by providing datafile

transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform

 operations on tablespaces, rather than the underlying individual datafiles.

Bigfile tablespaces are supported only for locally managed tablespaces with

automatic segment space management, with three exceptions: locally managed undo

tablespaces, temporary tablespaces, and the SYSTEM tablespace.

Notes:

•   Bigfile tablespaces are intended to be used with Automatic Storage Management

 (Oracle ASM) or other logical volume managers that supports striping or RAID, and

 dynamically extensible logical volumes.

•   Avoid creating bigfile tablespaces on a system that does not support striping

because of negative implications for parallel query execution and RMAN backup

parallelization.

•   Using bigfile tablespaces on platforms that do not support large file sizes is

 not recommended and can limit tablespace capacity. Refer to your operating system

 specific documentation for information about maximum supported file sizes.

    Notes部分ORACLE說使用BIGFILE TABLESPACE最好使用沒有檔案大小限制的ASM，否則BIGFILE TABLESPACE的大小將受檔案系統最大檔案大小的限制。

    後來將表空間從檔案系統改為ASM檔案系統，問題解決。