引言：
資料庫突然報： ORA-01654: unable to extend index BO.INDEX_indexname by 311072 in tablespace 錯誤，上網查原因，發現解決之道只有一個，就是增加tablespace的大小．因我的database的tablespace已經足夠大了，最後發現不是tablespace不足，而是參數錯誤．現歸納解決此問題的方法如下．
方法１：
當出現類似錯誤時，首先檢查tablespace的空間是否足夠大，如果不夠大，說明tablespace的空間不夠擴充了，這時候需要將tablespace的datafile的size變大，方法很簡單我就不講了，或增加新的datafile到此tablespace中，使用alter tablespace mytablespace add datafile 'XXX' size xxxx就ＯK啦．
方法2:
這就是我這此遇到的問題．我的datafile的size為2000m，而我的index的next extent為2G，pct increase為50,這樣一來下一個要擴充的extent為3G,而我的datafile的Size為２G,故無發找到連續3G的空間，當然會出錯．
問題找到了，解決當然很簡單，修改next extent 為128k,pct increase為0,問題解決.
不知道是誰設定的,真是個低級錯誤.
---------------------------------------------分割線---------------------------------------------
問題現象：
測試庫使用如下方式建立索引：
create index IDX_ANA_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
tablespace IDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
報錯：ORA-01654: unable to extend index GALT.IDX_OFFICE by 128 in tablespace IDX 改為預設建立：create index IDX_ANA_PNR_OFFICE on ANA (OFFICE_CITY, OFFICE_NO)
tablespace IDX;
查看SQL是：storage(initial 64Knext 1Mminextents 1maxextents unlimited);
問題追查：1、首先針對1654這個報錯，MOS是這樣介紹的：
Error: ORA-01654 Text: unable to extend index %s.%s by %s in tablespace %s -------------------------------------------------------------------------------Cause: Failed to allocate extent for index segment in tablespace.Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace
(1)、針對錶空間不足的情況，建議使用DBA_FREE_SPACE視圖進行查詢(Note: 121259.1提供了若干指令碼)。(2)、另外，針對索引的問題，DBA_INDEXES視圖則描述了下一個分區(NEXT_EXTENT)的大小，以及所有索引的百分比增長(PCT_INCREASE)。“next_extent”指的是試圖分配的區大小(也就是報錯中涉及的內容)。區分配計算：next_extent = next_extent * (1 + (pct_increase/100))在Concept中描述了為段分配區的演算法How Extents Are AllocatedOracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed. With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile’s bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.
MOS也提出了若干可能的解決方案：
Possible solutions:------------------- Manually coalesce adjacent free extents: ALTER TABLESPACE <tablespace name> COALESCE; The extents must be adjacent to each other for this to work.- Add a datafile: ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> <k|m>; - Resize the datafile: ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>; - Enable autoextend: ALTER DATABASE DATAFILE '<full path and file name>' AUTOEXTEND ON MAXSIZE UNLIMITED;- Defragment the Tablespace- Lower "next_extent" and/or "pct_increase" size: ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m> pctincrease <integer>);
下面這句話我認為是重點：“這個錯誤並未指出資料表空間中是否有足夠的空間，僅僅說明Oracle不能找到一個足夠大的連續空間用來匹配next extent。
2、另一篇文章“TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors”說明了各種關於“UNABLE TO CREATE / EXTEND”的錯誤。“unable to extend"的錯誤是指當沒有足夠連續的空間用來分配段的情況。 I. 提出瞭解決這種錯誤所需要的資訊：(1)、判斷報錯資料表空間中最大的連續空間是多少。SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '<tablespace name>';
這個SQL返回的是資料表空間最大允許的連續塊大小。(DBA_FREE_SPACE不會返回暫存資料表空間的資訊，可以參考“DBA_FREE_SPACE Does not Show Information about Temporary Tablespaces (文檔 ID 188610.1)”這篇文章會介紹如何查看暫存資料表空間的連續塊大小)。如果在這個報錯之後立即執行上述SQL，則返回的資料表空間中連續的最大塊會小於這個對象正在試圖分配的next extent的空間。 (2)、判斷NEXT_EXTENT大小。a) 對於PCT_INCREASE=0的字典管理資料表空間(DMT)或者使用統一UNIFORM區管理的本地管理資料表空間(LMT)，使用如下SQL：SELECT NEXT_EXTENT, PCT_INCREASE
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = <segment name>
AND SEGMENT_TYPE = <segment type>
AND OWNER = <owner>
AND TABLESPACE_NAME = <tablespace name>;其中segment_type會展示在錯誤資訊中，可能包含如下類型的segment：CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TYPE2 UNDO
TYPE2 UNDO (ORA-1651)同樣地，segment_name可以在錯誤資訊中找到。b) 對於使用SYSTEM|AUTOALLOCATE區管理的本地管理資料表空間(LMT)。沒有方法可以查詢它的next extent大小。只能查詢錯誤資訊，錯誤資訊中的塊數乘以資料表空間的塊大小，以此來判斷需要建立的區大小。c) 對於PCT_INCREASE>0的字典管理資料表空間(DMT)。SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<tablespace name>';
使用如下公式計算需要分配的區大小：extent size = next_extent * (1 + (pct_increase/100)
例如：next_extent = 512000
pct_increase = 50
next extent size = 512000 * (1 + (50/100)) = 512000 * 1.5 = 768000注意：ORA-01650 Rollback Segmentpct_increase僅用於Oracle若干早期版本，後面版本中復原段的pct_increase預設是0。ORA-01652 Temporary Segment
臨時段與資料表空間建立的儲存預設值相同。如果查詢出現錯誤，則需要判斷這個查詢語句是否儘可能地最優以完成排序。 (3)、判斷資料表空間是否包含了AUTOEXTENSIBLE，並已經達到MAXSIZ。對於資料檔案：SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name='<tablespace name> ';
對於臨時檔案：SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name='<tablespace name> ';
(4)、判斷哪種解決方案最優。��果NEXT EXTENT的容量(步驟2或3)大於空閑空間最大的連續塊，那麼“Manually Coalesce Adjacent Free Extents”是個選擇。如果coalesce後仍舊沒有足夠的連續空間，那麼可能需要其他的選項。如果資料表空間的資料檔案/臨時檔案的卷有足夠的空間，那麼添加資料檔案/臨時檔案或消除資料表空間片段化可能管用，將這個檔案添加到新卷中。如果資料表空間是AUTOEXTENSIBLE並且已經MAXSIZE，那麼需要提高最大容量(確認有足夠的卷空間)，或者添加資料檔案/臨時檔案，或者消除片段化。如果NEXT EXTENT的容量(步驟2或3)小於空閑空間最大的連續塊，那麼就需要聯絡Oracle支援。 II. 可能的解決方案：(1)、手工合并相鄰的空閑區。ALTER TABLESPACE <tablespace name> COALESCE;
(2)、將一個或多個資料檔案/臨時檔案修改為使用AUTOEXTEND。ALTER DATABASE DATAFILE|TEMPFILE '<full path and name>' AUTOEXTEND ON MAXSIZE <integer> <k | m | g |
注意：強烈建議明確MAXSIZE參數，防止資料檔案/臨時檔案消耗卷上的所有可用空間。(3)、添加資料檔案/臨時檔案。ALTER TABLESPACE <tablespace name> ADD DATAFILE|TEMPFILE '<full path and file name>' SIZE <integer> <k | m | g | t | p | e>;(4)、如果段是字典管理資料表空間，可以降低“next_extent”和/或“pct_increase”的大小。對於非臨時段和非分區段：ALTER <SEGMENT TYPE> <segment_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);
對於非臨時段和分區段：ALTER TABLE <table_name> MODIFY PARTITION <partition_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);
對於臨時段：ALTER TABLESPACE <tablespace name> DEFAULT STORAGE (initial <integer> <k | m | g | t | p | e> next <integer> <k | m | g | t | p | e> pctincrease <integer>);
(5)、重改資料檔案/臨時檔案的大小。ALTER DATABASE DATAFILE|TEMPFILE '<full path and file name>' RESIZE <integer> <k | m | g | t | p | e>;
(6)、消除資料表空間的片段。 附錄：和此類解決方案相關的報錯：ORA-1650: unable to extend rollback segment %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for a rollback segment in the tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1651: unable to extend save undo segment by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for saving undo entries for the indicated offline tablespace.
Action: Check the storage parameters for the SYSTEM tablespace. The tablespace needs to be brought back online so the undo can be applied.
ORA-1652: unable to extend temp segment by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1653: unable to extend table %s.%s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1654: unable to extend index %s.%s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for a cluster segment in tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1658: unable to create INITIAL extent for segment in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL
ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with smaller value for MINEXTENTS, NEXT or PCTINCREASE
ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s
Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s
Cause: Failed to allocate an extent for table subpartition segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s
Cause: Failed to allocate an extent for index subpartition segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s
Cause: An attempt was made to allocate an extent for LOB subpartition segment in tablespace, but the extent could not be allocated because there is not enough space in the tablespace indicated.
Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
總結：針對上面案例中的錯誤，總體講是空間不足導致的，之所以使用第二個SQL可以，原因可能就是這種參數值設定下的滿足可以空閑空間連續塊的容量，上面採用的是減小extent分配大小的方式，另外上面提到的擴大檔案、修改參數值、消除片段化等方法都可以嘗試使用。
ORA-01172、ORA-01151錯誤處理
ORA-00600 [2662]錯誤解決
ORA-01078 和 LRM-00109 報錯解決方案
ORA-00471 處理方法筆記
ORA-00314,redolog 損壞，或丟失處理方法
ORA-00257 歸檔日誌過大導致無法儲存的解決辦法
