use of max
SELECT A. TABLESPACE_NAME ,
ROUND((A. BYTES_ALLOC - NVL( B . BYTES_FREE , 0 )) / 1024 / 1024 ) MEGS_USED ,
ROUND(A. MAXBYTES / 1048576 ) MAX,
ROUND((A. BYTES_ALLOC - NVL( B . BYTES_FREE , 0 )) * 100 /A. MAXBYTES ) used_of_max
FROM (SELECT F . TABLESPACE_NAME ,
SUM( F . BYTES ) BYTES_ALLOC ,
SUM(DECODE( F . AUTOEXTENSIBLE , 'YES' , F . MAXBYTES , 'NO' , F . BYTES )) MAXBYTES
FROM DBA_DATA_FILES F
GROUP BY TABLESPACE_NAME ) A,
(SELECT F . TABLESPACE_NAME , SUM( F . BYTES ) BYTES_FREE
FROM DBA_FREE_SPACE F
GROUP BY TABLESPACE_NAME ) B WHERE A. TABLESPACE_NAME = B . TABLESPACE_NAME (+) use of allocation
SELECT total . tablespace_name ,
Round( total . MB , 2 ) AS Total_MB ,
Round( total . MB - nvl( free . MB , 0 ), 2 ) AS Used_MB ,
Round(nvl( free . MB , 0 ), 2 ) AS Free_MB ,
Round(( 1 - nvl( free . MB , 0 ) / total . MB ) * 100 , 2 ) || '%' AS Used_Pct ,
Round((nvl( free . MB , 0 ) / total . MB ) * 100 , 2 ) || '%' AS Free_Pct
FROM (SELECT tablespace_name ,
Sum( bytes ) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name ) free
full join
( SELECT tablespace_name ,
Sum( bytes ) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name ) total
on free . tablespace_name = total . tablespace_name ;
