Oracle无法新增数据，提示ORA-01653,无法通过8192（在表空间USERS中）扩展

近日，通过Webapi接口新增数据时，Oracle提示ORA-01653出错信息，经查询可能是单表数据太多造成表空间不足的原因，先看一下表数据量

再看下表空间使用信息

select a.tablespace_name,
       a.bytes / 1024 / 1024 "sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;

看结果USERS表空间已使用99.9%，知道原因我们就知道该从那个方向去找解决的方法，既然是表空间不够用，那就试着增加一下表空间

增加表空间语句：

alter database datafile '表空间位置' resize 新的尺寸

因此我们需要清楚表空间位置，执行下列语句查询

select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from sys.dba_data_files
 order by tablespace_name

得到表空间信息后，执行下列语句扩展空间到60G

又抛出错误

数据文件有32G大小限制，怎么办呢，再试一下新增数据文件，

这里新增一个和USER01一样的数据文件，不过这里size应该不用这么大，我执行成功后才这么想的，下次再试吧，哈哈。

这步执行完成后，问题解决，Webapi接口新增数据正常。本文中数据库版本为11g。

 

第一次遇到这个问题，在网上也查询了一下相关知识，这里收藏一下。

Oracle中数据文件大小及数量限制

在Oracle中，数据库是由实例和物理存储结构组成的。而物理存储结构是指存储在磁盘上的物理文件，包括数据文件（data file）、控制文件（control file）、联机重做日志（online redo log）、参数文件（spfile/pfile）、警告日志（alert log）、跟踪文件（trace file）等众多作用不同的文件所组成的。我们最关注的数据，则是保存在数据文件（data file）中。那我们在创建以及维护数据库时，该如何规划数据文件的大小和数量呢？这里面涉及较多的考量因素。主要有如下几点：


1、操作系统的限制


数据库是运行在操作系统之上的，操作系统是基础，因此，操作系统所能支持的最大文件容量和数量就成为数据库所能支持的限制。但不同操作系统之间，这个限制也是不同的。


以下是较为常见的几种操作系统对此的限制：


WINDOWS


最大数据块：16K


最大文件数量：20000个（数据块2K时）/40000个(数据块4K时）/65536个（数据块为8K或16K时）


最大文件容量：4GB（文件系统为FAT时）/ 64GB（文件系统为NTFS时）


UNIX和LINUX


最大数据块：32K (LINUX_X86为16K)


最大文件数量：65534个


2、ORACLE数据库的限制


每个数据库可管理的最大文件数量：65533个


每个表空间可管理的最大文件数量：取决于操作系统可同时打开的文件数量。通常是1022个。


每个数据文件的最大容量：该值等于 数据块大小 * 最大可管理的数据块数量


其中，数据块的大小最大不超过32K，一般取值是8K；可管理的数据块数量是2的22次方减1，约等于4M个块。因此，对于一个数据块大小为8K的数据文件，其最大不能超过32G。但是，若操作系统支持的单个文件最大容量小于此值，则以操作系统的最大容量为限。


3、参数DB_FILES的限制


参数DB_FILES指定了一个实例可以创建的最大文件数量。这个值可以被修改，但只有重启实例后，才会生效。DB_FILES设置得过低，可能造成不成添加新的数据文件的问题。设置得过高，会消耗更多的内存资源。


4、性能和便利性影响


a)  通过精心设计，将同一表空间内经常访问的对象放置在不同的数据文件中，并将这些数据文件放置到不同的磁盘通道上，可以改善I/O吞吐量。


b) 将经常改变的数据和不变的数据，放置到不同的数据文件中，备份时，可以只对改变的数据文件进行备份，从而减少备份和恢复的时间


注：从ORACLE 10g起，引入了大表空间的技术。所谓大表空间就是该表空间仅由1个数据文件组成。其优点如下：


1、显著增加了存储容量。大表空间可管理的数据块数量由传统的小表空间的2的22次方，提升到2的32次方。在同样采用8K大小的数据块大小时，其最大可管理空间为32T。


2、减少了数据库所需的数据文件的数量。


3、简化数据库管理。