表空间相关

--查询表空间使用情况

select t.tablespace_name, round(total/1024,2)||'G' as total_cap, round(free/1024,2)||'G' as free_cap, round(100*(1-(free/total)),3)||'%' used_rate

from (select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) t,

(select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) f

where t.tablespace_name=f.tablespace_name(+)

and round(100*(1-(free/total)),3)>70 --大于90%

order by round(100*(1-(free/total)),3) desc;

 

--查询哪张表占用率最高

select segment_name ,sum(bytes)/1024/1024||'M' as containcount from dba_segments

where tablespace_name='表空间名'

group by segment_name 

--having   sum(bytes)>1024*1024*500 --大于500m的

order by sum(bytes) desc;

 

 --查看表空间文件： 

SELECT tablespace_name,file_name,file_id, bytes/1024/1024/1024 

FROM dba_data_files 

WHERE 1=1

and tablespace_name like '%表空间%'

order by tablespace_name,bytes,file_id