查询oracle表空间大小、使用率（含临时表）

转载已于 2022-05-18 11:14:13 修改 · 493 阅读

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/gl230035/article/details/81010277?ops_request_misc=&request_id=&biz_id=102&utm_term=oracle%E4%B8%AD%E4%B8%B4%E6%97%B6%E8%A1%A8%E7%A9%BA%E9%97%B4%E5%A4%A7%E5%B0%8F%E5%92%8C%E6%98%AF%E4%BD%BF%E7%94%A8%E7%8E%87&utm_medium=distribute.pc_

文章标签：

#oracle #dba #数据库

于 2022-05-18 10:54:28 首次发布

该查询展示了数据库的数据文件和临时文件空间使用情况，包括总空间、空闲空间、已使用空间和使用率，帮助进行存储管理和优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

注意切换权限，直接执行就好。

select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
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)