[How to] Find the Tablespace size in Oracle

Here is the simple command which you can copy-paste and get output containing of all the TableSpaces, Size allocated to them and the Free Space left in that particular Database.

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

Output would be of the similar lines as below.

TABLESPACE_NAME          SIZEMB             FREEMB
——————————        ———-              ———-
SYSAUX                                3072               2776.88
UNDOTBS                             9605               7507.69
SYSTEM                               1024               777.25
DUBA                                    7168               5525.69
TEMP                                    1000

Leave a Reply

Your email address will not be published. Required fields are marked *