4. 表空间检查

• 查询所有表空间的使用率

  SELECT df.tablespace_name,
       ROUND((df.total_bytes - NVL(fs.free_bytes, 0)) / df.total_bytes * 100, 2) AS used_pct,
       ROUND(df.total_bytes / 1048576, 2) AS size_mb,
       ROUND((df.total_bytes - NVL(fs.free_bytes, 0)) / 1048576, 2) AS used_mb,
       ROUND(NVL(fs.free_bytes, 0) / 1048576, 2) AS free_mb
  FROM (SELECT tablespace_name, SUM(bytes) total_bytes
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) free_bytes
      FROM dba_free_space
      GROUP BY tablespace_name) fs
  WHERE df.tablespace_name = fs.tablespace_name(+)
  ORDER BY used_pct DESC;

  ==注意事项==

  1. 不包括临时表空间​：DBA_FREE_SPACE​ 不包含临时表空间的空间信息，临时表空间应查询 DBA_TEMP_FILES​ 和 V$TEMP_SPACE_HEADER。
  2. 不包含大文件表空间 (Bigfile) 的扩展潜力​：仅统计当前数据文件分配的大小，未考虑 AUTOEXTEND 的剩余上限。
  3. 空闲空间可能包含可回收的碎片​：DBA_FREE_SPACE 统计的是完全空闲的 extent，但表空间内部可能因碎片而无法分配大 extent（查询未反映该问题）。

• 表空间使用率（Top 10）==不准确==

  select * from (
  select tablespace_name,
         round(used_space*100/tablespace_size,2) used_pct,
         round(tablespace_size/1024/1024,2) size_mb,
         round(used_space/1024/1024,2) used_mb
  from dba_tablespace_usage_metrics
  order by used_pct desc
  ) where rownum <= 10;

• 数据文件与自动扩展

  select file_id, tablespace_name, file_name,
       round(bytes/1024/1024,2) size_mb,
       autoextensible, round(maxbytes/1024/1024,2) max_mb
  from dba_data_files
  order by tablespace_name, file_id;

• 临时文件扩展

  select tablespace_name, file_name,
       round(bytes/1024/1024,2) size_mb,
       autoextensible, round(maxbytes/1024/1024,2) max_mb
  from dba_temp_files;

• UNDO表空间检查

  • 查看UNDO区状态与使用率

  SELECT TABLESPACE_NAME, STATUS, COUNT(*) AS EXTENT_COUNT,
         ROUND(SUM(BYTES) / 1024 / 1024, 2) AS SIZE_MB,
         ROUND(SUM(BYTES) / SUM(SUM(BYTES)) OVER (PARTITION BY TABLESPACE_NAME) * 100, 2) AS PCT
  FROM DBA_UNDO_EXTENTS
  GROUP BY TABLESPACE_NAME, STATUS
  ORDER BY TABLESPACE_NAME, STATUS;

  • ​ACTIVE​​：当前被活跃事务所使用，​不可覆盖，是空间压力的主要来源。

  • ​UNEXPIRED​​：事务已提交，但保留时间未超过 UNDO_RETENTION​ 设置，​通常不可覆盖。

  • ​EXPIRED​​：事务已提交，且保留时间已超过 UNDO_RETENTION​ 设置，​可以被覆盖重用。

  • 计算 Undo 表空间实际使用率

  SELECT round(((SELECT (NVL(SUM(bytes), 0))
                 FROM dba_undo_extents
                 WHERE tablespace_name = (select value from v$parameter where lower(name) = 'undo_tablespace')
                   AND status IN ('ACTIVE', 'UNEXPIRED')) * 100) /
                (SELECT SUM(bytes) FROM dba_data_files
                 WHERE tablespace_name = (select value from v$parameter where lower(name) = 'undo_tablespace')), 2) AS PCT_INUSE
  FROM dual;

  • 计算 ACTIVE​ 和 UNEXPIRED 状态的区占整个 Undo 表空间的比例。

  • 查找长时间运行的 Undo 事务

  SELECT s.sid, s.username, s.program, t.name AS undo_segment_name,
         t.used_ublk * (SELECT value/1024/1024 FROM v$parameter WHERE name='db_block_size') AS undo_mb,
         t.start_time, t.status
  FROM v$transaction t, v$session s
  WHERE t.addr = s.taddr(+)
  ORDER BY t.used_ublk DESC;

  • 如果 ACTIVE 区占用过高，说明有长事务。使用下面的查询找出这些会话

  ‍