一、基础查询：表空间使用率概览
1.1 表空间使用率查询
-- 查看所有表空间使用情况（包含自动扩展信息）
SELECT
a.tablespace_name AS "表空间名称",
ROUND(a.bytes/1024/1024/1024, 2) AS "总大小(GB)",
ROUND(b.free_bytes/1024/1024/1024, 2) AS "空闲空间(GB)",
ROUND((a.bytes - b.free_bytes)/1024/1024/1024, 2) AS "已使用(GB)",
ROUND((a.bytes - b.free_bytes)/a.bytes * 100, 2) AS "使用率(%)",
c.autoextensible AS "自动扩展",
ROUND(c.maxbytes/1024/1024/1024, 2) AS "最大可扩展(GB)",
ROUND(c.increment_by * c.block_size/1024/1024, 2) AS "扩展增量(MB)",
COUNT(c.file_id) AS "数据文件数"
FROM
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) free_bytes
FROM dba_free_space
GROUP BY tablespace_name) b,
(SELECT tablespace_name, file_id, autoextensible, maxbytes, increment_by, block_size
FROM dba_data_files) c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, a.bytes, b.free_bytes, c.autoextensible,
ROUND(c.maxbytes/1024/1024/1024, 2),
ROUND(c.increment_by * c.block_size/1024/1024, 2)
ORDER BY "使用率(%)" DESC;
1.2 快速查询(11g 以上版本)
SELECT * FROM DBA_TABLESPACE_USAGE_METRICS ORDER BY 4;
二、详细分析：数据文件级别检查
2.1 数据文件详细信息查询
-- 查看每个数据文件的详细信息和自动扩展设置
SELECT
tablespace_name AS "表空间",
file_name AS "数据文件",
ROUND(bytes/1024/1024/1024, 2) AS "当前大小(GB)",
ROUND(maxbytes/1024/1024/1024, 2) AS "最大大小(GB)",
autoextensible AS "自动扩展",
ROUND(increment_by * block_size/1024/1024, 2) AS "扩展增量(MB)",
status AS "状态",
online_status AS "在线状态"
FROM dba_data_files
ORDER BY tablespace_name, file_name;
2.2 自动扩展配置检查
-- 检查自动扩展配置是否合理
SELECT
tablespace_name AS "表空间",
file_name AS "数据文件",
autoextensible AS "自动扩展",
CASE
WHEN autoextensible = 'YES' THEN '已启用自动扩展'
ELSE '未启用自动扩展 - 需要关注空间使用'
END AS "扩展状态",
ROUND(bytes/1024/1024, 2) AS "当前大小(MB)",
ROUND(maxbytes/1024/1024, 2) AS "最大大小(MB)",
ROUND((bytes/maxbytes)*100, 2) AS "扩展进度(%)"
FROM dba_data_files
WHERE autoextensible = 'YES'
ORDER BY tablespace_name;
三、临时表空间检查
3.1 临时表空间使用率查询
-- 临时表空间使用情况检查
SELECT
c.tablespace_name AS "临时表空间",
TO_CHAR(c.bytes / 1024 / 1024 / 1024, '99,999.999') AS "总大小(GB)",
TO_CHAR(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') AS "已使用(GB)",
TO_CHAR((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') AS "空闲空间(GB)",
TO_CHAR(d.bytes_used * 100 / c.bytes, '99.99') || '%' AS "使用率"
FROM
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) c,
(SELECT tablespace_name, SUM(bytes_cached) bytes_used
FROM v$temp_extent_pool
GROUP BY tablespace_name) d
WHERE c.tablespace_name = d.tablespace_name;
3.2 实时临时空间使用监控
-- 查看当前正在使用临时空间的SQL会话
SELECT
a.username,
a.sql_id,
a.SEGTYPE AS "段类型",
b.BYTES_USED/1024/1024/1024 AS "临时空间使用(GB)",
b.BYTES_FREE/1024/1024/1024 AS "剩余临时空间(GB)"
FROM V$TEMPSEG_USAGE a
JOIN V$TEMP_SPACE_HEADER b ON a.TABLESPACE = b.tablespace_name;
四、UNDO表空间检查
4.1 UNDO表空间使用率分析
-- UNDO表空间使用情况检查
SELECT
tablespace_name AS "UNDO表空间",
ROUND(SUM(bytes)/1024/1024/1024, 2) AS "总大小(GB)",
ROUND(SUM(CASE WHEN status IN ('ACTIVE','UNEXPIRED') THEN bytes ELSE 0 END)/1024/1024/1024, 2) AS "已使用(GB)",
ROUND(SUM(CASE WHEN status = 'EXPIRED' THEN bytes ELSE 0 END)/1024/1024/1024, 2) AS "可回收空间(GB)",
ROUND(SUM(CASE WHEN status IN ('ACTIVE','UNEXPIRED') THEN bytes ELSE 0 END) * 100 / SUM(bytes), 2) AS "使用率(%)"
FROM dba_undo_extents
GROUP BY tablespace_name;
4.2 UNDO自动扩展和保留策略
-- 检查UNDO表空间属性和自动扩展设置
SELECT
tablespace_name AS "表空间名称",
retention AS "保留策略",
ROUND(initial_extent/1024/1024, 2) AS "初始扩展(MB)",
ROUND(next_extent/1024/1024, 2) AS "下次扩展(MB)",
status AS "状态",
contents AS "内容类型"
FROM dba_tablespaces
WHERE contents = 'UNDO';
五、表空间增长趋势分析
5.1 表空间历史增长量统计
-- 统计表空间每日增长量（文档中提供的脚本）
SELECT
a.name AS "表空间名称",
b.tablespace_id,
TRUNC(TO_DATE(b.rtime, 'mm/dd/yyyy hh24:mi:ss')) AS "统计日期",
ROUND(MAX(b.tablespace_usedsize * 8 / 1024), 2) AS "使用大小(MB)",
ROUND(MAX(b.tablespace_usedsize * 8 / 1024 / 1024), 2) AS "使用大小(GB)"
FROM v$tablespace a,
(SELECT tablespace_id,
TRUNC(TO_DATE(rtime, 'mm/dd/yyyy hh24:mi:ss')) AS datetime,
ROUND(MAX(tablespace_usedsize * 8 / 1024), 2) AS "used_size(MB)",
ROUND(MAX(tablespace_usedsize * 8 / 1024 / 1024), 2) AS "used_size(GB)"
FROM dba_hist_tbspc_space_usage
WHERE TRUNC(TO_DATE(rtime, 'mm/dd/yyyy hh24:mi:ss')) > TRUNC(SYSDATE - 30)
GROUP BY tablespace_id, TRUNC(TO_DATE(rtime, 'mm/dd/yyyy hh24:mi:ss'))
ORDER BY tablespace_id, TRUNC(TO_DATE(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
WHERE a.ts# = b.tablespace_id
ORDER BY a.name, b.datetime;
SQL
5.2 表空间增长率和趋势预测
-- 表空间增长量和增长率分析
SELECT
A.NAME AS "表空间名称",
B.TABLESPACE_ID,
B.DATETIME AS "统计日期",
B.USED_SIZE_MB AS "使用大小(MB)",
B.INC_MB AS "日增长量(MB)",
CASE
WHEN SUBSTR(INC_RATE, 1, 1) = '.' THEN '0' || INC_RATE
WHEN SUBSTR(INC_RATE, 1, 2) = '-.' THEN '-0' || SUBSTR(INC_RATE, 2, LENGTH(INC_RATE))
ELSE INC_RATE
END AS "日增长率"
FROM V$TABLESPACE A,
(SELECT TABLESPACE_ID,
DATETIME,
USED_SIZE_MB,
(DECODE(PREV_USE_MB, 0, 0, USED_SIZE_MB) - PREV_USE_MB) AS INC_MB,
TO_CHAR(ROUND((DECODE(PREV_USE_MB, 0, 0, USED_SIZE_MB) - PREV_USE_MB) /
DECODE(PREV_USE_MB, 0, 1, PREV_USE_MB) * 100, 2)) || '%' AS INC_RATE
FROM (SELECT TABLESPACE_ID,
TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) DATETIME,
MAX(TABLESPACE_USEDSIZE * 8 / 1024) USED_SIZE_MB,
LAG(MAX(TABLESPACE_USEDSIZE * 8 / 1024), 1, 0)
OVER(PARTITION BY TABLESPACE_ID ORDER BY TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss'))) AS PREV_USE_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE
WHERE TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) > TRUNC(SYSDATE - 30)
GROUP BY TABLESPACE_ID, TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')))) B
WHERE A.TS# = B.TABLESPACE_ID
ORDER BY B.TABLESPACE_ID, DATETIME;
六、表空间碎片检查
6.1 表空间碎片分析
-- 检查表空间碎片情况（文档中提供的脚本）
SET LINES 200 PAGES 200 HEAD ON FEEDBACK OFF
COL MES FOR A100
WITH
t1 AS (SELECT TABLESPACE_NAME, ROUND(USED_SPACE/128) mb, ROUND(TABLESPACE_SIZE/128) totalmb, ROUND(USED_PERCENT,2) USED_PERCENT
FROM dba_tablespace_usage_metrics),
t1_filter AS (SELECT * FROM t1 WHERE totalmb-mb < 100*POWER(2,10) AND USED_PERCENT > 90),
t2 AS (SELECT fs.tablespace_name, fs.bytes/POWER(2,20) mb, COUNT(*) cnt
FROM DBA_FREE_SPACE fs, t1_filter t1f
WHERE fs.tablespace_name = t1f.tablespace_name
GROUP BY fs.tablespace_name, fs.bytes/POWER(2,20)),
t3 AS (SELECT tablespace_name,
CASE WHEN mb < 1 THEN '<1MB' WHEN mb < 4 THEN '<4MB' ELSE '>=4MB' END size_range,
SUM(mb) mb, SUM(cnt) cnt
FROM t2
GROUP BY tablespace_name, CASE WHEN mb < 1 THEN '<1MB' WHEN mb < 4 THEN '<4MB' ELSE '>=4MB' END),
t4 AS (SELECT tablespace_name, size_range, mb, cnt,
RATIO_TO_REPORT(mb) OVER(PARTITION BY tablespace_name) rtp
FROM t3),
t5 AS (SELECT tablespace_name,
NVL(mb_less_1,0) mb_less_1, NVL(cnt_less_1,0) cnt_less_1, NVL(rtp_less_1,0) rtp_less_1,
NVL(mb_less_4,0) mb_less_4, NVL(cnt_less_4,0) cnt_less_4, NVL(rtp_less_4,0) rtp_less_4,
NVL(mb_more_4,0) mb_more_4, NVL(cnt_more_4,0) cnt_more_4, NVL(rtp_more_4,0) rtp_more_4
FROM (SELECT tablespace_name,
MAX(CASE WHEN size_range = '<1MB' THEN mb END) mb_less_1,
MAX(CASE WHEN size_range = '<1MB' THEN cnt END) cnt_less_1,
MAX(CASE WHEN size_range = '<1MB' THEN rtp END) rtp_less_1,
MAX(CASE WHEN size_range = '<4MB' THEN mb END) mb_less_4,
MAX(CASE WHEN size_range = '<4MB' THEN cnt END) cnt_less_4,
MAX(CASE WHEN size_range = '<4MB' THEN rtp END) rtp_less_4,
MAX(CASE WHEN size_range = '>=4MB' THEN mb END) mb_more_4,
MAX(CASE WHEN size_range = '>=4MB' THEN cnt END) cnt_more_4,
MAX(CASE WHEN size_range = '>=4MB' THEN rtp END) rtp_more_4
FROM t4
GROUP BY tablespace_name))
SELECT 'Tablespace :' || tablespace_name ||
' is no continuous space greater than or equal to 4M, or the total is less than 5G, and the table space usage rate exceeds 90%' AS MES
FROM t5
WHERE NOT (cnt_more_4 >= 1 AND mb_more_4 > 5120);
七、自动扩展管理和优化建议
7.1 自动扩展配置检查
-- 检查需要优化自动扩展配置的表空间
SELECT
tablespace_name AS "表空间",
file_name AS "数据文件",
autoextensible AS "自动扩展",
ROUND(bytes/1024/1024, 2) AS "当前大小(MB)",
ROUND(maxbytes/1024/1024, 2) AS "最大大小(MB)",
ROUND(increment_by * block_size/1024/1024, 2) AS "扩展增量(MB)",
CASE
WHEN autoextensible = 'NO' THEN '建议启用自动扩展'
WHEN increment_by * block_size < 10485760 THEN '扩展增量过小，建议调整'
WHEN maxbytes = 0 THEN '未设置最大限制，存在风险'
ELSE '配置正常'
END AS "优化建议"
FROM dba_data_files
WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
ORDER BY tablespace_name;
7.2 自动扩展配置修改示例
-- 启用数据文件自动扩展
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
-- 修改自动扩展参数
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
-- 禁用自动扩展
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf'
AUTOEXTEND OFF;
八、监控脚本和告警阈值设置
8.1 表空间监控脚本（包含告警）
-- 表空间监控告警脚本
SET SERVEROUTPUT ON
DECLARE
v_warning_threshold NUMBER := 85; -- 警告阈值 85%
v_critical_threshold NUMBER := 95; -- 严重阈值 95%
v_count NUMBER := 0;
BEGIN
FOR rec IN (
SELECT tablespace_name,
ROUND((total_bytes - free_bytes) / total_bytes * 100, 2) usage_pct
FROM (SELECT tablespace_name, SUM(bytes) total_bytes
FROM dba_data_files GROUP BY tablespace_name) t,
(SELECT tablespace_name, SUM(bytes) free_bytes
FROM dba_free_space GROUP BY tablespace_name) f
WHERE t.tablespace_name = f.tablespace_name
)
LOOP
IF rec.usage_pct >= v_critical_threshold THEN
DBMS_OUTPUT.PUT_LINE('CRITICAL: Tablespace ' || rec.tablespace_name ||
' usage is ' || rec.usage_pct || '% - IMMEDIATE ACTION REQUIRED!');
v_count := v_count + 1;
ELSIF rec.usage_pct >= v_warning_threshold THEN
DBMS_OUTPUT.PUT_LINE('WARNING: Tablespace ' || rec.tablespace_name ||
' usage is ' || rec.usage_pct || '% - Monitor closely');
v_count := v_count + 1;
END IF;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('All tablespaces are within normal usage limits');
END IF;
END;
/
九、总结与最佳实践
9.1 表空间管理最佳实践
定期监控
：建议每天检查表空间使用率
合理设置自动扩展
：根据业务增长设置合适的扩展增量
设置最大限制
：避免数据文件无限增长
预留缓冲空间
：保持使用率在80%以下
及时处理告警
：设置合理的监控阈值
9.2 关键监控指标
使用率 > 85%
：发出警告，需要关注
使用率 > 95%
：严重告警，立即处理
自动扩展禁用
：高风险，需要评估
碎片率过高
：影响性能，需要整理
通过以上脚本和监控方案，可以全面掌握Oracle数据库表空间的使用情况，及时发现潜在问题，确保数据库稳定运行。