1. 表級空間使用詳情(DBMS_SPACE)
DECLARE
unf NUMBER; unfb NUMBER;
fs1 NUMBER; fs1b NUMBER;
fs2 NUMBER; fs2b NUMBER;
fs3 NUMBER; fs3b NUMBER;
fs4 NUMBER; fs4b NUMBER;
full NUMBER; fullb NUMBER;
BEGIN
dbms_space.space_usage('&1', '&2', 'TABLE',
unf, unfb, fs1, fs1b, fs2, fs2b, fs3, fs3b, fs4, fs4b, full, fullb);
dbms_output.put_line('unformatted_blocks:' || unf);
dbms_output.put_line('full_blocks(0% free):' || full);
dbms_output.put_line('fs1_blocks(0-25% free):' || fs1);
dbms_output.put_line('fs2_blocks(25-50% free):' || fs2);
dbms_output.put_line('fs3_blocks(50-75% free):' || fs3);
dbms_output.put_line('fs4_blocks(75-100% free):' || fs4);
END;
/
-- 參數 &1=owner, &2=table_name
2. 表空間使用歷史(每天首次採樣)
SELECT b.name,
a.rtime,
a.tablespace_usedsize,
a.tablespace_size,
ROUND(100 * a.tablespace_usedsize / a.tablespace_size) used_percent
FROM dba_hist_tbspc_space_usage a,
(SELECT t2.name,
MIN(rtime) rtime,
MIN(tablespace_id) tablespace_id
FROM dba_hist_tbspc_space_usage t1
JOIN v\$tablespace t2 ON t1.tablespace_id = t2.ts#
WHERE t2.name = UPPER('&1')
GROUP BY name, SUBSTR(rtime,1,10)
) b
WHERE a.tablespace_id = b.tablespace_id
AND a.rtime = b.rtime
ORDER BY a.rtime;
3. 表空間增長趨勢預測
SELECT * FROM TABLE(dbms_space.object_growth_trend('ECC_CC', 'YTKC_ORDERRELEVANCE', 'TABLE'));
部署建議
- 將腳本封裝為 Shell + SQL*Plus 定時任務,輸出 CSV/HTML 報告
- 結合閾值告警(如使用率 > 85% 或增長率 > 20%/月)
- 歷史數據保留 12 個月以上,支撐容量規劃