Oracle 資料庫表空間使用狀況查詢與擴充指南

前言

Oracle 資料庫的表空間使用狀況需定期監控，當空閒比例過低時，應及時擴充表空間以避免業務中斷。本文提供兩種常用的查詢 SQL，並說明表空間不足時的處理步驟。

方法一：結合 DBA_DATA_FILES 與 DBA_FREE_SPACE 查詢

此方法分別統計各表空間的總大小與剩餘空間，再計算空閒比例。

SELECT dbf.tablespace_name,
       dbf.totalspace "總量(M)",
       dbf.totalblocks 總塊數,
       dfs.freespace "剩餘總量(M)",
       dfs.freeblocks "剩餘塊數",
       (dfs.freespace / dbf.totalspace) * 100 "空閒比例"
FROM   (SELECT t.tablespace_name,
             SUM(t.bytes) / 1024 / 1024 totalspace,
             SUM(t.blocks) totalblocks
      FROM   dba_data_files t
      GROUP  BY t.tablespace_name) dbf,
       (SELECT tt.tablespace_name,
             SUM(tt.bytes) / 1024 / 1024 freespace,
             SUM(tt.blocks) freeblocks
      FROM   dba_free_space tt
      GROUP  BY tt.tablespace_name) dfs
WHERE  TRIM(dbf.tablespace_name) = TRIM(dfs.tablespace_name);

方法二：使用 V$ 視圖查詢

透過系統視圖 V$DATAFILE 與 V$TABLESPACE 獲取總空間，搭配 DBA_FREE_SPACE 計算已用與剩餘空間。

SELECT Total.name "Tablespace Name",
       Free_space,
       (total_space - Free_space) Used_space,
       total_space
FROM   (SELECT tablespace_name,
             SUM(bytes / 1024 / 1024) Free_Space
      FROM   sys.dba_free_space
      GROUP  BY tablespace_name) Free,
       (SELECT b.name,
             SUM(bytes / 1024 / 1024) TOTAL_SPACE
      FROM   sys.v_$datafile a, sys.v_$tablespace b
      WHERE  a.ts# = b.ts#
      GROUP  BY b.name) Total
WHERE  Free.Tablespace_name = Total.name;

表空間不足時的處理步驟

1. 找出對應的數據文件及路徑

   SELECT * FROM dba_data_files t WHERE t.tablespace_name = 'ARD';

2. 調整數據文件大小

   ALTER DATABASE DATAFILE '完整路徑的數據文件名稱' RESIZE ***M;

3. 新增數據文件

   ALTER TABLESPACE 表空間名稱
   ADD DATAFILE '完整路徑的數據文件名稱' ***M;

最佳實務建議

• 建議維持表空間免費百分比在 10% 以上。
• 當低於 10% 時，應增加數據文件或調整現有文件大小。
• 單一數據文件建議不超過 2GB，以利管理與備份。