Oracle DBA 日常管理實用 SQL 查詢集：表空間、資料檔、控制檔、Redo Log、歸檔

一鍵查詢腳本庫

1. 表空間總容量 (GB)

SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_data_files
GROUP BY tablespace_name;

2. 表空間剩餘空間

SELECT tablespace_name, 
       SUM(bytes) AS free_bytes
FROM dba_free_space
GROUP BY tablespace_name;

3. 所有表空間清單

SELECT tablespace_name FROM dba_tablespaces;

4. 所有資料檔路徑

SELECT file_name FROM dba_data_files;

5. 控制檔路徑

SELECT value FROM v$parameter WHERE name = 'control_files';

6. Redo Log 檔案成員

SELECT member FROM v$logfile;

7. 歸檔日誌目的地

SELECT value FROM v$parameter WHERE name = 'log_archive_dest';

進階組合：表空間使用率儀表板

SELECT a.tablespace_name,
       ROUND(a.total_gb,2) total_gb,
       ROUND(b.free_gb,2) free_gb,
       ROUND((a.total_gb - b.free_gb)/a.total_gb*100,1) pct_used
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024/1024 total_gb
  FROM dba_data_files GROUP BY tablespace_name
) a
JOIN (
  SELECT tablespace_name, SUM(bytes)/1024/1024/1024 free_gb
  FROM dba_free_space GROUP BY tablespace_name
) b USING (tablespace_name)
ORDER BY pct_used DESC;