1. 表空間使用率統計表
CREATE TABLE system.tbs_detail AS
SELECT a.tablespace_name,
a.bytes/1024/1024 "Sum_MB",
(a.bytes-b.bytes)/1024/1024 "used_MB",
b.bytes/1024/1024 "free_MB",
ROUND(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC;
2. 應用表空間數據文件使用情況
CREATE TABLE system.datafile_space AS
SELECT a.tablespace_name, a.file_name,
a.bytes/1024/1024 total,
b.sum_free/1024/1024 free
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) sum_free FROM dba_free_space GROUP BY file_id) b
WHERE a.file_id = b.file_id
AND a.tablespace_name IN (SELECT tablespace_name FROM system.tbs_detail
WHERE (tablespace_name LIKE '%CQLT%' OR tablespace_name LIKE '%CQST%'
OR tablespace_name LIKE 'TS%' OR tablespace_name LIKE 'IDX%'
OR tablespace_name LIKE '%HX%')
AND "Sum_MB" > 100);
3. 生成數據文件 RESIZE 腳本(保留 100M 緩衝)
SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' RESIZE ' ||
ROUND(total - free + 100, 0) || 'M;'
FROM system.datafile_space;
4. ASM 磁盤組使用情況巡檢腳本
sqlplus / as sysdba <
使用建議
- 將上述腳本封裝為 Shell/Python 定時任務,每日執行並告警
- RESIZE 腳本執行前務必人工核對,避免誤縮小活躍文件
- ASM 監控可擴展為
v$asm_disk 級別,監控單盤不平衡