解決 ORA-01653 表空間擴展失敗：場景分析與處理實戰

ORA-01653 錯誤成因與解決方案全解析

在 Oracle 資料庫維護中，ORA-01653: unable to extend table ... in tablespace ... 是極為常見的錯誤，意味著表空間無法自動擴展以容納新數據。本文將透過實際場景重現、診斷查詢與修復步驟，幫助 DBA 快速定位並解決此問題。

錯誤重現場景

假設資料檔（.dbf）位於 E 槽，且剩餘空間僅 200MB。當嘗試大量寫入數據時，便會觸發錯誤。以下為模擬腳本：

-- 1. 建立測試表
DROP TABLE bigtab;
CREATE TABLE bigtab AS SELECT rownum AS "id", a.* FROM dba_objects a;

DROP TABLE smalltab;
CREATE TABLE smalltab AS SELECT rownum AS "id", a.* FROM dba_tables a;

-- 2. 迴圈插入大量數據，耗盡表空間
DECLARE
  n NUMBER;
BEGIN
  FOR n IN 1..100 LOOP
    INSERT INTO bigtab SELECT rownum AS "id", a.* FROM dba_objects a;
  END LOOP;
  COMMIT;
END;
/ -- 此處拋出 ORA-01653

核心診斷：檢查自動擴展屬性

首要任務是確認資料檔是否啟用 AUTOEXTEND。執行以下查詢：

SELECT 
  T.TABLESPACE_NAME,
  D.FILE_NAME,
  D.AUTOEXTENSIBLE,
  D.BYTES/1024/1024 AS CURRENT_MB,
  D.MAXBYTES/1024/1024 AS MAX_MB,
  D.STATUS
FROM DBA_TABLESPACES T
JOIN DBA_DATA_FILES D ON T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

若 AUTOEXTENSIBLE 為 NO，或 MAXBYTES 已達上限，則無法自動增長。同時，可用以下查詢評估表空間使用率：

SELECT 
  UPPER(F.TABLESPACE_NAME) AS "表空間名",
  D.TOT_GROOTTE_MB AS "表空間大小(M)",
  D.TOT_GROOTTE_MB - F.TOTAL_BYTES AS "已使用空間(M)",
  TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2), '990.99') AS "使用比%",
  F.TOTAL_BYTES AS "空閒空間(M)",
  F.MAX_BYTES AS "最大連續塊(M)"
FROM (
  SELECT TABLESPACE_NAME, 
         ROUND(SUM(BYTES)/(1024*1024),2) TOTAL_BYTES,
         ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
  FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
) F
JOIN (
  SELECT DD.TABLESPACE_NAME, 
         ROUND(SUM(DD.BYTES)/(1024*1024),2) TOT_GROOTTE_MB
  FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME
) D ON D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

解決步驟

1. 釋放磁碟空間：清理 E 槽，確保至少預留 2GB 以上可用空間。
2. 啟用自動擴展並移除上限：對目標資料檔執行：

ALTER DATABASE DATAFILE 'E:\DATA_DATA.DBF' 
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

執行後，表空間將能按 50MB 遞增自動擴展，直至磁碟耗盡，徹底消除 ORA-01653 風險。

最佳實踐建議

• 定期監控表空間使用率，設定閾值告警（如 80%）。
• 關鍵表空間建議啟用 AUTOEXTEND ON MAXSIZE UNLIMITED，並配合大文件表空間使用。
• 結合 DBA_FREE_SPACE 監控碎片化程度，必要時執行 ALTER TABLESPACE ... COALESCE。

掌握上述診斷與修復流程，即可從容應對表空間擴展失敗，保障業務連續性。