ORA-3233資料表空間相關問題處理

問題現象：

測試庫使用如下方式建立索引：

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 64K

next 1M

 minextents 1

maxextents 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 Allocated

Oracle 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 Segment

pct_increase僅用於Oracle若干早期版本，後面版本中復原段的pct_increase預設是0。

ORA-01652 Temporary Segment
 

臨時段與資料表空間建立的儲存預設值相同。

如果查詢出現錯誤，則需要判斷這個查詢語句是否儘可能地最優以完成排序。

Oracle ORA-01555 快照過舊 說明

ORA-01078 和 LRM-00109 報錯解決方案

ORA-01555超長的Query Duration時間

ORA-00471 處理方法筆記

ORA-00314,redolog 損壞，或丟失處理方法

ORA-00257 歸檔日誌過大導致無法儲存的解決辦法

(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支援。

1 2 下一頁