ORA-01658：oracle创建表空间时指定的UNLIMITED真的是UNLIMITED吗？

背景介绍

最近，同事说在执行ETL的过程中oracle报错 ORA-01658: unable to create INITIAL extent for segment in tablespace XXX。
从oracle给出的提示中不难发现这是因为表空间满了导致，但同事疑惑的地方在于，该表空间在创建时设置了AUTOEXTEND，MAXSIZE也指定为UNLIMITED，使用df命令查看文件系统的空闲情况发现也还是有足够的空间的，那么为什么表空间没有自动扩展呢？
其实这里有个坑人的地方，oracle在创建表空间语句中使用的CREATE TABLESPACE ... MAXSIZE UNLIMITED并不能真正创建一个无限增长的文件。

原因分析

观前提醒：接下来的分析默认是指smallfile的表空间，如果表空间是bigfile的则不适用。如何判断表空间属于哪种类型呢？首先，smallfile是默认类型，如果在创建表空间时没有显式声明则默认是smallfile的。其次，可以通过SELECT TABLESPACE_NAME, BIGFILE FROM DBA_TABLESPACES来确认smallfile还是bigfile

表空间与数据文件

oracle中的表空间(tablespace)是一个逻辑上的概念，实际上还是通过数据文件(datafile)来存储数据，但很多人经常混淆它们的概念。
它们之间对应关系是一对多的关系，即一个表空间可以有多个数据文件，一个数据文件属于一个表空间。
在这个例子中，该表空间只拥有一个数据文件，表空间满了说明其实是所属的数据文件满了。但是如同开头所提到的，在创建表空间时指定了自动扩展和UNLIMITED为什么不起作用呢？（题外话，这里自动扩展的应该是数据文件）

数据文件与块

熟悉oracle的朋友可能知道oracle中有表空间，段，区段和块(data block)。其中块是最小的逻辑存储单元，也是oracle每次I/O的最小单元。
下图是oracle官方文档中对块结构的描述，可见数据都是存放在块中：

oracle中使用块来管理数据文件，之所以这样做是用来屏蔽底层操作系统的差异。数据块的大小可以从2KB~32KB，但必须是操作系统块大小的倍数（主要是为了提升I/O性能）。而导致此次数据文件无法扩展的原因，也正出在块上面。

数据文件中块的数量限制

如前所述，oracle使用块来管理数据文件，但每个数据文件中块的数量是有限制的。根据官网的描述，一个数据文件中可以有2~4194303个块。参见Oracle官方对于一个数据文件中块数目的描述 Physical Database Limits
也就是说，其实一个数据文件的大小等于4194303*块大小。数据块的大小可以通过show parameter db_block_size查看，在本例中，块大小是8KB，带入上面公式可以得出一个数据文件的最大值是32GB左右。让同事查询了一下dba_data_files，发现该数据文件确实已经增长到了32GB，因为达到了块的上限，所以就无法扩展了。

解决方案

知道原因后，解决起来就很容易了，有两种方案这里分别说一下：

增加数据文件（推荐）

既然表空间和数据文件是一对多的关系，而一个数据文件已经增长到了上限，那么再增加一个数据文件就可以达到扩展表空间的目的了。通过下面的语句可以为表空间XXX增加一个新的数据文件。

ALTER TABLESPACE XXX ADD DATAFILE ...

然而，需要注意的是一个表空间的数据文件是有上限的，最多可以由1024个数据文件组成，不过足以应付大多数情况了。

迁移到bigfile表空间

除了传统的smallfile表空间外，oracle还有一种bigfile表空间。前面提到的一个表空间可以有1024个数据文件，每个数据文件可以有4194303个块。而对于bigfile表空间来说，只能有一个数据文件，但每个数据文件所包含的块可以是smallfile的1024倍。
采用此种方案，需要首先创建一个bigfile表空间，在其中建立对应的表，然后通过DBMS_REDEFINITION这个包进行表的迁移。

采用这种方案可以避免添加大量数据文件的操作，但其实表空间的容量上限没有改变，同时使用bigfile表空间也要考虑到操作系统、扩展性等问题，因此这里个人不推荐使用这种方式。关于bigfile表空间的更多信息可以参考oracle官方文档：3 Tablespaces, Datafiles, and Control Files

关于块大小的一个误区

有朋友可能会问：既然数据文件的大小取决于块大小乘一个常数，那么能不能通过调整块大小来改变数据文件大小呢？
首先，对于已经存在的数据库来说，除非新建一个数据库，否则不能改变块大小。
其次即使是新建数据库时，也不应该为了数据文件的大小而影响块大小。因为块是oracle进行I/O的基本单位，过大的块会导致读取和写入时携带大量不必要的数据。
选择块大小的依据一般是基于场景的，比如OLTP这种事务操作较多、实时性较高的场景可以使用较小的块，比如8KB（但也不推荐使用太小的块，因为每个块会固定存储一部分元数据，太小的块意味着存储效率的下降）；而OLAP或数据仓库等以存储和查询为主，对于实时性要求不太高的场景可以使用更大的块，如16KB或32KB。