oracle 自动扩展 限制,大文件表空间受操作系统限制无法自动扩展

之前只知道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 tablespacePICS

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

by 4096 in tablespacePICS

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

by 64 in tablespacePICS

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

by 4096 in tablespacePICS

而此时数据文件大小将近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文件系统，问题解决。