Query table space utilization

Time:2019-11-11

Query tablespace utilization — consider self growth, where utilization considers tablespace utilization relative to threshold

select a.tablespace_name, total || ‘M’, free+shortNum|| ‘M’ free, total-free-shortNum || ‘M’ as used,
Round((free+shortNum)/total * 100, 3) as “FREE%”,Round((total – free-shortNum)/total * 100, 3) as “USED%” from
(select tablespace_name,autoextensible,case when t.autoextensible=’YES’ then (maxbytes-bytes)/1024/1024 else 0 end shortNum,case when t.autoextensible=’YES’ then sum(maxbytes)/1024/1024 else sum(bytes)/1024/1024 end as total from dba_data_files t group by tablespace_name,autoextensible,maxbytes,bytes) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name order by a.tablespace_name

Query results

 

Set the maximum threshold value for automatic growth. No matter what the self increment value is, when the disk space is enough, the final size of the table space = the threshold value

 

No what you want? Go to: https://www.cnblogs.com/lipera/p/6201434.html