Tablespace sql

select a.tablespace_name, round(a.bytes_alloc / 1024 / 1024, 2) as current_size
,round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) as free_size
,round(a.bytes_alloc - nvl(b.bytes_free, 0) / 1024 / 1024, 2) as used_si
,round((nvl(b.bytes_free, 0) / a.bytes_alloc)*100, 2) as free_rate
,100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc)*100, 2) as used_rate
--        ,round(a.MAXBYTES/ 1024 / 1024, 2) as current_size
from ( select f.tablespace_name, sum(f.bytes) as bytes_alloc,
        sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes))
    from dba_data_files f
    group by f.tablespace_name) a,
    ( select f.tablespace_name, sum(f.bytes) as bytes_free                
    from dba_free_space f
    group by f.tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)


SELECT A.TABLESPACE_NAME TS_NAME,
SUM(A.BYTES)/1024/1024 AS TOT_MB,
round(NVL(SUM(SZ_MB),0)) AS FREE_MB,
round(SUM(A.BYTES)/1024/1024 - NVL(SUM(SZ_MB),0)) AS USE_MB,
round(NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/1024/1024.) ) * 100,100),2) AS USAGE
FROM DBA_DATA_FILES A,
( SELECT FILE_ID, SUM(BYTES)/1024/1024 AS SZ_MB
FROM DBA_FREE_SPACE
GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID (+)
GROUP BY A.TABLESPACE_NAME;


select name, rtime, tablespace, size/1024/1024, tablespace, maxsize/1024/1024, table
from dba_hist_tbspc_space_usage, v$tablespace where 1=1
--and
order by 1, 2;