oracle 统计表空间使用率经典脚本

set linesize 200;
set pagesize 500;
col TABLESPACE_NAME for a20;
col USED_PERCENT for a10;
col Total/MAX_PRCENT for a20;
select Total.Tname TABLESPACE_NAME,
Total.Total_Size “TOTAL(MB)”,
Total.Total_Size - nvl(Used.free_size,0) “USED(MB)” ,
nvl(Used.Free_size,0) “FREE(MB)”,
(1-(Round( nvl(Used.Free_size,0) / Total.Total_Size, 4))) * 100 || ‘%’ USED_PERCENT,
nvl2(Total.Max_Size,Total.max_size-Total.total_size,NULL) AS “MAX-Total” ,
nvl2(Total.Max_Size,Round((Total.Total_Size - nvl(Used.free_size,0))/Total.Max_Size,4)*100 ,NULL) || ‘%’ AS “Total/MAX_PRCENT”
from (
select tablespace_name as TName,
round(sum(user_bytes) / (1024 * 1024), 1) as Total_Size,
round(sum(nvl2(nullif(MAXBYTES,0),MAXBYTES,user_bytes)) / (1024 * 1024), 1) as Max_Size
from dba_data_files
group by tablespace_name) Total,
(
select tablespace_name as TName,
round(sum(bytes) / (1024 * 1024), 1) as Free_size
from dba_free_space
group by tablespace_name) Used
where Total.TName = Used.TName(+) order by nvl(Used.Free_size,0) / Total.Total_Size ASC;