ORA-1653 oracle單個資料檔案最大限制

    早上開發反映無法向資料庫表中填寫日誌，提示資料庫ORA-1653錯誤。
    查詢資料庫告警日誌，報錯如下：
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 。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26972107/viewspace-774143/，如需轉載，請註明出處，否則將追究法律責任。