oracle 查询 tablespace 及其空间使用情况

SQL 查询语句：

SELECT t.tablespace_name
     ,to_char((t.total_space - NVL(f.free_space,0)), '999,999') "USED (MB)"
     ,to_char(NVL(f.free_space,0), '999,999') "FREE (MB)"
     ,to_char(t.total_space, '999,999') "TOTAL (MB)"
     ,to_char((round((NVL(f.free_space,0) / t.total_space) * 100)), 999) || ' %' per_free
FROM (SELECT tablespace_name
           ,round(SUM(blocks *
                      (SELECT VALUE / 1024 FROM v$parameter WHERE NAME = 'db_block_size') / 1024)) free_space
      FROM dba_free_space
      GROUP BY tablespace_name) f
   ,(SELECT tablespace_name, round(SUM(bytes / 1048576)) total_space
     FROM dba_data_files
     GROUP BY tablespace_name) t
WHERE f.tablespace_name(+) = t.tablespace_name
UNION ALL
SELECT t.tablespace_name
     ,to_char((t.total_space - NVL(f.free_space,0)), '999,999') "USED (MB)"
     ,to_char(NVL(f.free_space,0), '999,999') "FREE (MB)"
     ,to_char(t.total_space, '999,999') "TOTAL (MB)"
     ,to_char((round((NVL(f.free_space,0) / t.total_space) * 100)), 999) || ' %' per_free
FROM (SELECT tablespace_name, round(SUM(bytes_free) / (1024 * 1024), 2) free_space
      FROM v$temp_space_header
      GROUP BY tablespace_name) f
   ,(SELECT tablespace_name, round(SUM(bytes) / (1024 * 1024), 2) total_space
     FROM dba_temp_files
     GROUP BY tablespace_name) t
WHERE f.tablespace_name(+) = t.tablespace_name
ORDER BY 5;

SQL 查询结果：

SQL语句详解：

这段SQL代码是用来查询Oracle数据库中各个表空间的使用情况的。它包括了永久表空间和临时表空间的使用情况，并且显示了已使用空间、剩余空间、总空间以及剩余空间百分比。下面是对这个查询的详细解释：

1. 查询分为两部分，通过UNION ALL合并结果。第一部分查询永久表空间的使用情况，第二部分查询临时表空间的使用情况。

2. 在永久表空间的查询中：

   • t子查询从dba_data_files视图中获取每个表空间的总大小（以MB为单位）。
   • f子查询从dba_free_space视图中计算每个表空间的剩余空间（以MB为单位），这里使用了db_block_size参数来获取块的大小，并将其转换为MB。

3. 在临时表空间的查询中：

   • t子查询从dba_temp_files视图中获取每个临时表空间的总大小。
   • f子查询从v$temp_space_header视图中获取每个临时表空间的剩余空间。

4. 对于两种类型的表空间（永久和临时），查询都会计算以下列：

   • USED (MB)：已使用的空间（总空间减去剩余空间）。
   • FREE (MB)：剩余的空间。
   • TOTAL (MB)：总空间。
   • per_free：剩余空间百分比，使用round((NVL(f.free_space,0) / t.total_space) * 100)来计算，并且以百分比形式展示。

5. 最后，使用ORDER BY 5根据剩余空间百分比对结果进行排序。

6. 在整个查询中，使用了NVL函数来处理可能的NULL值，保证即使没有剩余空间数据也能正确计算已使用空间。

7. 使用了外连接f.tablespace_name(+) = t.tablespace_name来确保即使某些表空间没有对应的剩余空间记录也能在结果中显示。

8. 查询结果将以文本形式显示各列，其中数值型数据格式化为带有逗号的形式，便于阅读。

这个查询对于DBA来说非常有用，因为它可以直接显示出数据库中各个表空间的使用状况，帮助DBA监控和管理存储资源。