Oracle数据库查询各表空间的占用比例

本文主要是介绍Oracle数据库查询各表空间的占用比例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

目录

1、查询各表数据记录和数据大小

2、查询数据库已有表空间的大小

3、查询某表空间下各表占用突然间的大小 

---

---

1、查询各表数据记录和数据大小

select a.table_name "表名",a.num_rows "数据记录",b.total "总大小（MB)" from user_tables a,
(select t.segment_name, sum(bytes)/1024/1024 as total from user_segments t 
group by t.segment_name) b where a.table_name = b.segment_name 
order by b.total desc

2、查询数据库已有表空间的大小

select a.tablespace_name "表空间名称"，total "表空间总大小（MB)",
free "剩余表空间大小(MB)"， (total-free) "表空间占用大小(MB)",
round((total-free)/total * 100,2) || '%' "已使用表空间百分比" 
from (select tablespace_name, sum(bytes)/1024/1024 total from 
dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1014 free from 
dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by (total - free) desc

3、查询某表空间下各表占用突然间的大小 

with e as (select sum(bytes)/1024/1024 total from dba_data_files where tablespace_name='mytablespace'),d as (select c.table_name, sum(c.used_space) used_space from (select a.index_name,a.table_name,a.num_rows,a.sample_size, b.used_space from dba_indexs a,(select owner, segment_name, segment_type, sum(bytes)/1024/1024 used_space from dba_segments where owner = 'myowner' and tablespace_name = 'mytablespace' group by owner,segment_name,segment_typeorder by segment_type) b where a.index_name = b.segment_name and a.owner = b.ownerand a.tablespace_name = 'mytabespace'order by used_space desc) c group by c.table_nameselect d.table_name "表名" , e.total "总空间(MB)" , d.used_space "已使用空间(MB)"round(d.used_space/e.total * 100,2) || '%' "已使用空间的百分比"from d,e group by d.used_space desc

这篇关于Oracle数据库查询各表空间的占用比例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---