View the Oracle tablespace size and the remaining space size

Select f. tablespace_name,
A. total,
U. used,
F. free,
Round (u. used/a. total) * 100) "% USED ",
Round (f. free/a. total) * 100) "% FREE"
From (select tablespace_name, sum (bytes/(1024*1024) total
From dba_data_files
Group by tablespace_name),
(Select tablespace_name, round (sum (bytes/(1024*1024) used
From dba_extents
Group by tablespace_name) u,
(Select tablespace_name, round (sum (bytes/(1024*1024) free
From dba_free_space
Group by tablespace_name) f
Where a. tablespace_name = f. tablespace_name
And a. tablespace_name = u. tablespace_name;

 

The following method is faster:

Select a. tablespace_name,
A. bytes/1024/1024 "Sum MB ",
(A. bytes-B. bytes)/1024/1024 "used MB ",
B. bytes/1024/1024 "free MB ",
Round (a. bytes-B. bytes)/a. bytes) * 100, 2) "percent_used"
From (select tablespace_name, sum (bytes) bytes
From dba_data_files
Group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest
From dba_free_space
Group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-B. bytes)/a. bytes) desc;