今天在一台Linux服务器上跑的Oracle扩展表空间时出现了ORA-01144错误：

[oracle@erpdb ~]$ sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 2 09:33:45 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database datafile 5 resize 32768M; 
alter database datafile 5 resize 32768M
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks

出现这个错误的原因是要调整的大小超出了Oracle database file size的限制，文档里说这个最大size “Operating system dependent. Limited by maximum operating system file size; typically 2^22 or 4 MB blocks”。

一般我们的数据库都是block size都是8k，而4MB个blocks正好是32个GB。

要注意的是只有smallfile tablespace才有这个限制，bigfile tablespace的容量限制会比这个数值大得多，具体可查看相关文档。

在这种情况下，我们可以用增加数据文件的方式来扩展表空间就可以了：

SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup
  2  /

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB
------------ ------------------------------ ---------- ----------
           1 DGDEV                              292974     166457
           2 DGSTUDY                            292974     168839

SQL> alter tablespace erp add datafile '+DGDEV' size 4096M;

Tablespace altered.

SQL> alter database datafile 9 resize 9216m;

Database altered.

2009/11/02 12:11 - Oracle

Reader's Comments

1. robin | 2009/11/02 17:21

   其实实际的数据文件大小限制应该是(2^22-1)*db_block_size

2. banping | 2009/11/02 23:24

   robin强

3. robin | 2009/11/03 09:23

   偶尔露个脸,嘿嘿