SELECT F.TABLESPACE_NAME tbname,T.TOTAL_SPACE,F.FREE_SPACE,T.MAX_SPACE,
TO_CHAR(((T.TOTAL_SPACE-F.FREE_SPACE)/T.TOTAL_SPACE)*100,'99.99')||'%' percent,
TO_CHAR(((T.TOTAL_SPACE-F.FREE_SPACE)/T.MAX_SPACE)*100,'99.99')||'%' percent_max
FROM (
SELECT TABLESPACE_NAME,SUM (BLOCKS*8192/(1024*1024)) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(SELECT TABLESPACE_NAME,
SUM(BYTES/1048576) TOTAL_SPACE,
SUM(DECODE(AUTOEXTENSIBLE,'YES',MAXBYTES/1048576,BYTES/1048576)) MAX_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
col tablespace_name for a40
col CUR_PCT for a10
col EXT_PCT for a10
select
tablespace,
current_used_mb,
total_mb,
can_extend_mb,
trunc(current_used_mb/total_mb*100,2)||'%' cur_pct,
trunc(current_used_mb/can_extend_mb*100,2)||'%' ext_pct,
count_file
from
(select
a.tablespace_name tablespace,
trunc((b.total-a.free)/1024/1024,2) current_used_mb,
trunc(b.total/1024/1024,2) total_mb,
trunc(b.extent_total/1024/1024) can_extend_mb,
b.count_file count_file
from
(select
tablespace_name,
sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,
sum(nvl(bytes,0)) total,
sum(decode(maxbytes,0,bytes,maxbytes)) extent_total,
count(file_name) count_file
from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name)
order by EXT_PCT desc;