Oracle 空间使用监控

表空间监控

表空间使用情况

表空间是 Oracle 数据库中存储数据的基本单位，监控表空间使用情况是日常运维的重要任务。以下是常用的监控 SQL 语句：

sql

-- 查看表空间使用情况
SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_space_mb, 
       ROUND(used_space, 2) AS used_space_mb, 
       ROUND(free_space, 2) AS free_space_mb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024 AS total_space,
         SUM(bytes - free_bytes)/1024/1024 AS used_space,
         SUM(free_bytes)/1024/1024 AS free_space
  FROM dba_tablespace_usage_metrics
  GROUP BY tablespace_name
)
ORDER BY used_percent DESC;

-- 查看临时表空间使用情况
SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_space_mb, 
       ROUND(used_space, 2) AS used_space_mb, 
       ROUND(free_space, 2) AS free_space_mb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024 AS total_space,
         SUM(bytes - free_bytes)/1024/1024 AS used_space,
         SUM(free_bytes)/1024/1024 AS free_space
  FROM dba_temp_free_space
  GROUP BY tablespace_name
)
ORDER BY used_percent DESC;

-- 查看撤销表空间使用情况
SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_space_mb, 
       ROUND(used_space, 2) AS used_space_mb, 
       ROUND(free_space, 2) AS free_space_mb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024 AS total_space,
         SUM(bytes - free_bytes)/1024/1024 AS used_space,
         SUM(free_bytes)/1024/1024 AS free_space
  FROM (
    SELECT tablespace_name, bytes, 
           CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END - bytes AS free_bytes
    FROM dba_data_files
    WHERE tablespace_name LIKE '%UNDO%'
  )
  GROUP BY tablespace_name
)
ORDER BY used_percent DESC;

表空间增长率

监控表空间增长率对于预测空间需求和避免空间不足非常重要。以下是创建历史记录表和计算增长率的方法：

sql

-- 创建表空间使用历史记录表
CREATE TABLE tablespace_growth_history (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  collection_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  tablespace_name VARCHAR2(30),
  total_space_mb NUMBER,
  used_space_mb NUMBER,
  free_space_mb NUMBER,
  used_percent NUMBER
);

-- 收集表空间使用情况
INSERT INTO tablespace_growth_history (tablespace_name, total_space_mb, used_space_mb, free_space_mb, used_percent)
SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_space_mb, 
       ROUND(used_space, 2) AS used_space_mb, 
       ROUND(free_space, 2) AS free_space_mb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024 AS total_space,
         SUM(bytes - free_bytes)/1024/1024 AS used_space,
         SUM(free_bytes)/1024/1024 AS free_space
  FROM dba_tablespace_usage_metrics
  GROUP BY tablespace_name
);

-- 计算表空间增长率
SELECT t1.tablespace_name,
       t1.collection_time AS current_time,
       t2.collection_time AS previous_time,
       t1.used_space_mb AS current_used,
       t2.used_space_mb AS previous_used,
       ROUND(t1.used_space_mb - t2.used_space_mb, 2) AS growth_mb,
       ROUND((t1.used_space_mb - t2.used_space_mb)/t2.used_space_mb*100, 2) AS growth_percent,
       ROUND((t1.used_space_mb - t2.used_space_mb) * (24*60*60) / (EXTRACT(SECOND FROM (t1.collection_time - t2.collection_time)) + 
                                                               EXTRACT(MINUTE FROM (t1.collection_time - t2.collection_time)) * 60 + 
                                                               EXTRACT(HOUR FROM (t1.collection_time - t2.collection_time)) * 3600 + 
                                                               EXTRACT(DAY FROM (t1.collection_time - t2.collection_time)) * 86400), 2) AS mb_per_day
FROM tablespace_growth_history t1
JOIN tablespace_growth_history t2 ON t1.tablespace_name = t2.tablespace_name
WHERE t1.collection_time > t2.collection_time
AND t2.collection_time = (
  SELECT MAX(collection_time)
  FROM tablespace_growth_history
  WHERE tablespace_name = t1.tablespace_name
  AND collection_time < t1.collection_time
)
ORDER BY t1.tablespace_name, t1.collection_time;

数据文件监控

数据文件使用情况

数据文件是表空间的物理存储载体，监控数据文件使用情况有助于了解存储空间的详细分布：

sql

-- 查看数据文件使用情况
SELECT file_name, 
       tablespace_name, 
       ROUND(bytes/1024/1024, 2) AS size_mb,
       ROUND(user_bytes/1024/1024, 2) AS used_mb,
       ROUND((bytes - user_bytes)/1024/1024, 2) AS free_mb,
       ROUND((user_bytes/bytes)*100, 2) AS used_percent,
       autoextensible,
       CASE WHEN autoextensible = 'YES' THEN ROUND(maxbytes/1024/1024, 2) ELSE NULL END AS max_size_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;

-- 查看临时文件使用情况
SELECT file_name, 
       tablespace_name, 
       ROUND(bytes/1024/1024, 2) AS size_mb,
       autoextensible,
       CASE WHEN autoextensible = 'YES' THEN ROUND(maxbytes/1024/1024, 2) ELSE NULL END AS max_size_mb
FROM dba_temp_files
ORDER BY tablespace_name, file_name;

-- 查看数据文件 I/O 性能
SELECT df.name AS file_name,
       ts.name AS tablespace_name,
       f.phyrds AS physical_reads,
       f.phywrts AS physical_writes,
       ROUND((f.phyrds * 8192)/1024/1024, 2) AS read_mb,
       ROUND((f.phywrts * 8192)/1024/1024, 2) AS write_mb,
       ROUND(f.time waited/100, 2) AS wait_time_sec
FROM v$filestat f,
     v$datafile df,
     v$tablespace ts
WHERE f.file# = df.file#
AND df.ts# = ts.ts#
ORDER BY (f.phyrds + f.phywrts) DESC;

数据文件自动扩展监控

数据文件自动扩展是 Oracle 的重要特性，但需要监控以避免出现问题：

sql

-- 查看自动扩展的数据文件
SELECT file_name, 
       tablespace_name, 
       ROUND(bytes/1024/1024, 2) AS current_size_mb,
       ROUND(maxbytes/1024/1024, 2) AS max_size_mb,
       ROUND((bytes/maxbytes)*100, 2) AS used_percent_of_max
FROM dba_data_files
WHERE autoextensible = 'YES'
AND maxbytes > 0
ORDER BY used_percent_of_max DESC;

-- 监控数据文件自动扩展事件
SELECT * FROM v$event_name WHERE name LIKE '%autoextend%';

-- 查看数据文件自动扩展历史
SELECT * FROM dba_hist_file_extended WHERE tablespace_name IS NOT NULL
ORDER BY extended_timestamp DESC;

段空间监控

大型段监控

大型段可能会影响数据库性能和空间使用，需要重点监控：

sql

-- 查看前 20 个大型段
SELECT owner, 
       segment_name, 
       segment_type, 
       tablespace_name, 
       ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- 查看特定用户的大型段
SELECT owner, 
       segment_name, 
       segment_type, 
       tablespace_name, 
       ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE owner = 'HR'
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

-- 查看表空间中的大型段
SELECT owner, 
       segment_name, 
       segment_type, 
       ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

段空间使用情况

监控段空间使用情况有助于了解对象的存储效率：

sql

-- 查看表的空间使用情况
SELECT owner, 
       table_name, 
       tablespace_name, 
       num_rows, 
       ROUND(bytes/1024/1024, 2) AS table_size_mb,
       ROUND(blocks*8192/1024/1024, 2) AS allocated_mb,
       ROUND((bytes - (num_rows * avg_row_len))/1024/1024, 2) AS unused_mb,
       ROUND((num_rows * avg_row_len)/bytes*100, 2) AS used_percent
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND num_rows > 0
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- 查看索引的空间使用情况
SELECT owner, 
       index_name, 
       table_name, 
       index_type, 
       tablespace_name, 
       ROUND(bytes/1024/1024, 2) AS index_size_mb
FROM dba_indexes i, dba_segments s
WHERE i.owner = s.owner
AND i.index_name = s.segment_name
AND i.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY s.bytes DESC
FETCH FIRST 20 ROWS ONLY;

空间监控工具

Oracle Enterprise Manager

Oracle Enterprise Manager (OEM) 是 Oracle 官方提供的图形化管理工具，提供了全面的空间监控功能：

空间管理页面：
- 导航到 "存储" → "表空间"
- 查看表空间使用情况和增长率
- 设置空间使用告警
自动空间顾问：
- 导航到 "存储" → "自动空间顾问"
- 分析表空间增长趋势
- 获取空间优化建议

自定义监控脚本

对于需要自动化监控的场景，可以创建自定义的监控脚本：

sql

-- 创建空间监控脚本
CREATE OR REPLACE PROCEDURE monitor_tablespace_usage
AS
  v_subject VARCHAR2(100);
  v_message VARCHAR2(4000);
BEGIN
  -- 检查表空间使用情况
  FOR ts IN (
    SELECT tablespace_name, 
           ROUND(total_space, 2) AS total_space_mb, 
           ROUND(used_space, 2) AS used_space_mb, 
           ROUND(free_space, 2) AS free_space_mb, 
           ROUND((used_space/total_space)*100, 2) AS used_percent
    FROM (
      SELECT tablespace_name, 
             SUM(bytes)/1024/1024 AS total_space,
             SUM(bytes - free_bytes)/1024/1024 AS used_space,
             SUM(free_bytes)/1024/1024 AS free_space
      FROM dba_tablespace_usage_metrics
      GROUP BY tablespace_name
    )
    WHERE ROUND((used_space/total_space)*100, 2) > 80
  ) LOOP
    v_subject := '表空间使用告警: ' || ts.tablespace_name;
    v_message := '表空间: ' || ts.tablespace_name || CHR(10) ||
                '总空间: ' || ts.total_space_mb || ' MB' || CHR(10) ||
                '已用空间: ' || ts.used_space_mb || ' MB' || CHR(10) ||
                '剩余空间: ' || ts.free_space_mb || ' MB' || CHR(10) ||
                '使用百分比: ' || ts.used_percent || '%';
    
    -- 发送邮件通知
    -- 这里需要配置 UTL_MAIL 或其他邮件发送机制
    -- EXECUTE IMMEDIATE 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
    -- UTL_MAIL.SEND(...);
    
    -- 记录告警
    INSERT INTO space_alert_log (alert_time, tablespace_name, used_percent, message)
    VALUES (SYSTIMESTAMP, ts.tablespace_name, ts.used_percent, v_message);
  END LOOP;
END;
/

-- 调度空间监控作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'SPACE_MONITORING_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'monitor_tablespace_usage',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=6',
    enabled         => TRUE,
    comments        => '每 6 小时执行一次空间监控'
  );
END;
/

第三方监控工具

除了 Oracle 官方工具外，还有许多第三方监控工具可以用于空间监控：

Zabbix：
- 使用 Oracle 监控模板
- 配置表空间使用监控项
- 设置空间使用触发器和告警
Nagios：
- 使用 check_oracle_health 插件
- 监控表空间使用情况
- 集成邮件和短信告警
Prometheus + Grafana：
- 使用 Oracle exporter
- 配置空间使用指标采集
- 创建空间监控仪表板

空间优化策略

表空间优化

合理的表空间规划和维护是空间管理的基础：

合理规划表空间：
- 根据数据类型和使用模式创建专用表空间
- 为大型对象创建单独的表空间
- 合理设置表空间初始大小和自动扩展参数
表空间维护：
- 定期检查表空间使用情况
- 及时扩展表空间或添加数据文件
- 考虑使用本地管理表空间和自动段空间管理

数据文件优化

数据文件的优化可以提高存储效率和性能：

数据文件布局：
- 将不同类型的数据文件分散到不同的物理磁盘
- 避免单个数据文件过大（建议不超过 32GB）
- 合理设置数据文件的自动扩展参数
数据文件管理：
- 监控数据文件的 I/O 性能
- 考虑使用 ASM 管理数据文件
- 定期检查数据文件的完整性

段空间优化

段空间的优化可以减少空间浪费和提高存储效率：

大型段管理：
- 考虑对大型表进行分区
- 定期重组碎片段
- 监控和管理大型索引
空间回收：
- 定期收集表和索引的统计信息
- 对删除大量数据的表执行段收缩
- 考虑使用段空间自动管理

常见问题（FAQ）

Q1: 如何设置表空间使用告警？

A1: 可以通过以下方式设置表空间使用告警：

使用 Oracle Enterprise Manager：
- 导航到 "存储" → "表空间"
- 选择表空间，点击 "编辑"
- 在 "阈值" 标签页设置警告和严重阈值

使用 DBMS_SERVER_ALERT：

sql

-- 设置表空间使用告警
EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
  metric_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
  warning_operator => DBMS_SERVER_ALERT.OPER_GE,
  warning_value => '80',
  critical_operator => DBMS_SERVER_ALERT.OPER_GE,
  critical_value => '90',
  observation_period => 1,
  consecutive_occurrences => 1,
  instance_name => NULL,
  object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
  object_name => 'USERS'
);

使用自定义脚本：
- 创建定期执行的监控脚本
- 当表空间使用超过阈值时发送通知

Q2: 如何预测表空间耗尽时间？

A2: 可以通过以下方法预测表空间耗尽时间：

使用历史数据：
- 收集表空间使用的历史数据
- 计算平均增长率
- 基于当前使用情况和增长率预测耗尽时间
使用自动空间顾问：
- 导航到 "存储" → "自动空间顾问"
- 分析表空间增长趋势
- 获取空间耗尽预测

使用 SQL 计算：

sql

-- 基于历史数据预测表空间耗尽时间
SELECT t1.tablespace_name,
       t1.used_space_mb AS current_used,
       t1.free_space_mb AS current_free,
       ROUND((t1.used_space_mb - t2.used_space_mb) / 
             (EXTRACT(DAY FROM (t1.collection_time - t2.collection_time))), 2) AS daily_growth_mb,
       CASE WHEN (t1.used_space_mb - t2.used_space_mb) > 0 THEN
            ROUND(t1.free_space_mb / 
                  ((t1.used_space_mb - t2.used_space_mb) / 
                   (EXTRACT(DAY FROM (t1.collection_time - t2.collection_time)))), 1)
       ELSE NULL
       END AS days_until_full
FROM tablespace_growth_history t1,
     tablespace_growth_history t2
WHERE t1.tablespace_name = t2.tablespace_name
AND t1.collection_time = (
  SELECT MAX(collection_time)
  FROM tablespace_growth_history
  WHERE tablespace_name = t1.tablespace_name
)
AND t2.collection_time = (
  SELECT MAX(collection_time)
  FROM tablespace_growth_history
  WHERE tablespace_name = t1.tablespace_name
  AND collection_time < t1.collection_time
)
ORDER BY days_until_full;

Q3: 如何处理表空间碎片？

A3: 处理表空间碎片的方法：

段重组：

sql

-- 对表进行重组
ALTER TABLE table_name MOVE;

-- 重建索引
ALTER INDEX index_name REBUILD;

-- 对分区表进行重组
ALTER TABLE partitioned_table MOVE PARTITION partition_name;

段收缩：

sql

-- 启用行移动
ALTER TABLE table_name ENABLE ROW MOVEMENT;

-- 收缩段
ALTER TABLE table_name SHRINK SPACE;

-- 收缩段并压缩
ALTER TABLE table_name SHRINK SPACE COMPACT;

表空间重组：
- 创建新的表空间
- 将对象移动到新表空间
- 删除旧表空间

Q4: 如何监控临时表空间的使用？

A4: 监控临时表空间使用的方法：

查看临时表空间使用情况：

sql

SELECT tablespace_name, 
       ROUND(total_space, 2) AS total_space_mb, 
       ROUND(used_space, 2) AS used_space_mb, 
       ROUND(free_space, 2) AS free_space_mb, 
       ROUND((used_space/total_space)*100, 2) AS used_percent
FROM (
  SELECT tablespace_name, 
         SUM(bytes)/1024/1024 AS total_space,
         SUM(bytes - free_bytes)/1024/1024 AS used_space,
         SUM(free_bytes)/1024/1024 AS free_space
  FROM dba_temp_free_space
  GROUP BY tablespace_name
);

查看临时段使用情况：

sql

SELECT username, 
       session_addr, 
       session_num, 
       tablespace, 
       segtype, 
       ROUND(bytes/1024/1024, 2) AS size_mb
FROM v$tempseg_usage
ORDER BY size_mb DESC;

监控临时表空间的等待事件：

sql

SELECT event, 
       total_waits, 
       time_waited, 
       average_wait
FROM v$system_event
WHERE event LIKE '%temp%';

Q5: 如何优化 ASM 存储的空间使用？

A5: 优化 ASM 存储空间使用的方法：

ASM 磁盘组管理：
- 合理规划磁盘组的大小和冗余级别
- 监控 ASM 磁盘组的使用情况
- 及时添加磁盘以扩展空间

ASM 空间回收：

sql

-- 检查 ASM 磁盘组使用情况
SELECT name, 
       state, 
       type, 
       total_mb/1024 AS total_gb,
       free_mb/1024 AS free_gb,
       ROUND((1 - free_mb/total_mb)*100, 2) AS used_percent
FROM v$asm_diskgroup;

-- 重新平衡磁盘组
ALTER DISKGROUP data REBALANCE POWER 5;

ASM 文件管理：
- 监控大型 ASM 文件
- 考虑使用 ASM 模板管理文件属性
- 定期检查和清理不需要的 ASM 文件

Q6: 如何处理数据文件自动扩展失败的情况？

A6: 处理数据文件自动扩展失败的方法：

原因分析：
- 检查文件系统空间是否不足
- 检查用户权限是否足够
- 检查 Oracle 是否达到了最大数据文件大小限制
解决方案：
- 扩展文件系统空间
- 添加新的数据文件到表空间
- 考虑使用大文件表空间
预防措施：
- 监控数据文件的自动扩展设置
- 定期检查文件系统空间
- 合理设置数据文件的大小和自动扩展参数

Q7: 如何监控和管理 LOB 段的空间使用？

A7: 监控和管理 LOB 段空间使用的方法：

查看 LOB 段使用情况：

sql

SELECT owner, 
       table_name, 
       column_name, 
       segment_name, 
       ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_lobs l, dba_segments s
WHERE l.segment_name = s.segment_name
AND l.owner = s.owner
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

LOB 段优化：
- 考虑将 LOB 数据存储在单独的表空间
- 对于大型 LOB，考虑使用 SecureFile LOB
- 定期检查和优化 LOB 段

LOB 段空间回收：

sql

-- 对 LOB 段执行收缩
ALTER TABLE table_name MODIFY LOB (lob_column) (SHRINK SPACE);

-- 重建 LOB 段
ALTER TABLE table_name MOVE LOB (lob_column) STORE AS (TABLESPACE lob_tablespace);

Q8: 如何设置空间监控的最佳实践？

A8: 空间监控的最佳实践：

监控频率：
- 生产环境：每小时监控一次
- 关键数据库：每 15-30 分钟监控一次
- 非生产环境：每天监控一次
告警阈值：
- 警告阈值：75-80%
- 严重阈值：85-90%
- 紧急阈值：95% 以上
监控内容：
- 表空间使用情况和增长率
- 数据文件的自动扩展状态
- 临时表空间的使用情况
- 大型段的增长情况
响应流程：
- 收到警告后：评估增长趋势，准备扩展计划
- 收到严重告警后：立即执行扩展操作
- 定期回顾：分析空间使用模式，优化空间分配
自动化：
- 实现空间监控的自动化
- 配置自动告警通知
- 考虑实现自动扩展脚本

通过以上监控和优化策略，可以有效管理 Oracle 数据库的存储空间，避免空间不足导致的数据库故障，确保数据库的稳定运行。Oracle 空间监控是数据库运维的重要组成部分，需要持续关注和优化，以保证数据库系统的高效运行。

Oracle 空间使用监控 ​

表空间监控 ​

表空间使用情况 ​

表空间增长率 ​

数据文件监控 ​

数据文件使用情况 ​

数据文件自动扩展监控 ​

段空间监控 ​

大型段监控 ​

段空间使用情况 ​

空间监控工具 ​

Oracle Enterprise Manager ​

自定义监控脚本 ​

第三方监控工具 ​

空间优化策略 ​

表空间优化 ​

数据文件优化 ​

段空间优化 ​

常见问题（FAQ） ​

Q1: 如何设置表空间使用告警？ ​

Q2: 如何预测表空间耗尽时间？ ​

Q3: 如何处理表空间碎片？ ​

Q4: 如何监控临时表空间的使用？ ​

Q5: 如何优化 ASM 存储的空间使用？ ​

Q6: 如何处理数据文件自动扩展失败的情况？ ​

Q7: 如何监控和管理 LOB 段的空间使用？ ​

Q8: 如何设置空间监控的最佳实践？ ​