ORA-1653 oracle单个数据文件最大限制

转自：http://blog.itpub.net/26972107/viewspace-774143/

    查询数据库告警日志，报错如下：
ORA-1653: unable to extend table CMTSMAIN.T0501_LOG by 128 in                 tablespace CMTSMAIN_TS 
ORA-1653: unable to extend table CMTSMAIN.T0501_LOG by 8192 in                 tablespace CMTSMAIN_TS 
ORA-1653: unable to extend table CMTSMAIN.T0502_LOG by 128 in                 tablespace CMTSMAIN_TS 
ORA-1653: unable to extend table CMTSMAIN.T0502_LOG by 8192 in                 tablespace CMTSMAIN_TS 
   查询数据库表空间使用率，如下
SQL> select total.tablespace_name,round(total.MB,2) as Total_MB,round(total.MB-free.MB, 2) as Used_MB,round((1-free.MB/total.MB)*100, 2) as Used_Pct from (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,(select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name;
TABLESPACE_NAME                  TOTAL_MB    USED_MB   USED_PCT
------------------------------ ---------- ---------- ----------
CMTSMAIN_TS                      32757.06      32730      99.92
  表空间使用率为99.92%。
  查询表空间的数据文件，及自动扩展属性
SQL> select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE  from dba_data_files;
TABLESPACE_NAME                FILE_NAME                                AUT
------------------------------ ---------------------------------------- ---
CMTSMAIN_TS                    /u01/data/orcl/CMTSMAIN_TS01.dbf         YES
  此表空间只包含一个数据文件，文件大小已经达到oracle单个数据文件的最大限制。
 因此， 添加数据文件
SQL> alter tablespace CMTSMAIN_TS add datafile '/u01/data/orcl/CMTSMAIN_TS02.dbf' size 3G autoextend on;
  和研发人员沟通后，此表空间有两张表进行大量数据插入，因此增大数据文件至30G
SQL> alter database datafile '/u01/data/orcl/CMTSMAIN_TS02.dbf' resize 30G;
  同时继续增加两个数据文件
SQL> alter tablespace CMTSMAIN_TS add datafile '/u01/data/orcl/CMTSMAIN_TS03.dbf' size 30G autoextend on;
SQL> alter tablespace CMTSMAIN_TS add datafile '/u01/data/orcl/CMTSMAIN_TS04.dbf' size 30G autoextend on;
  查看数据库表空间使用率
SQL> select total.tablespace_name,round(total.MB,2) as Total_MB,round(total.MB-free.MB, 2) as Used_MB,round((1-free.MB/total.MB)*100, 2) as Used_Pct from (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,(select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name=total.tablespace_name;
TABLESPACE_NAME                  TOTAL_MB    USED_MB   USED_PCT
------------------------------ ---------- ---------- ----------
CMTSMAIN_TS                     124917.06      34269      27.43
  涉及知识点，单个数据文件大小最大为32G，原理如下 
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2^22) blocks.
  数据库数据块是大小是8k ，
SQL> show parameter block
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
  那么能创建的最大的数据文件大小是：2^22*8K=32G,而单个数据文件已经达到32G了，超过限制了，无法扩展，所以报错。而2^22是由于Oracle的Rowid中使用22位来代表Block号，这22位最多只能代表2^22-1(4194303)个数据块。
  又及：为了扩展数据文件的大小，Oracle10g中引入了大文件表空间，在大文件表空间下，Oracle使用32位来代表Block号，也就是说，大文件表空间下每个文件最多可以容纳4G个Block。
那么也就是说当Block_size为8k时，数据文件可以达到32T 。