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_type
						order by segment_type) b 
				where a.index_name = b.segment_name 
				and a.owner = b.owner
				and a.tablespace_name = 'mytabespace'
				order by used_space desc
				) c 
			group by c.table_name
	select 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