The script below lists all tablespace names in the database and their associated information. Run the script using SQL*Plus as user DBA. 


set echo off
column TSNAME format a31;
column TYPE format a11;
column EXT_MGT format a12;
column STATUS format a9;
column SIZE_MB format 99,999.99;
column FREE_MB format 99,999.99;
column USED_MB format 99,999.99;
set pagesize 100 lines 120
set feed off heading off
select
'**************************************************************'
from dual;
select 'TABLESPACE REPORT' from
dual;
select
'***********************************'
from dual;
set feed on heading on
break on REPORT;
compute sum label 'Total' of SIZE_MB on REPORT;
compute sum label 'Total Free MB' of FREE_MB on REPORT;
compute sum label 'Total Used MB' of USED_MB on REPORT;
select
 a.TABLESPACE_NAME tsname                               ,
 a.contents Type                                        ,
 a.extent_management EXT_MGT                            ,
 b.bytes/(1024*1024) SIZE_MB                            ,
 (c.free_bytes/(1024*1024)) FREE_MB                     ,
 (b.bytes-c.free_bytes)/(1024*1024) USED_MB             ,
 a.STATUS STATUS              
from dba_tablespaces a, 
(select tablespace_name, sum(bytes) bytes
        from dba_data_files
        group by tablespace_name 
union
select tablespace_name, sum(bytes) bytes
        from dba_temp_files
        group by tablespace_name) b,
(select dfs.tablespace_name, sum(dfs.bytes) free_bytes,
(sum(ddf.bytes)-sum(dfs.bytes)) used_bytes
        from dba_free_space dfs, dba_data_files ddf
        where dfs.tablespace_name=ddf.tablespace_name
        group by dfs.tablespace_name
union
select tablespace_name, sum(BYTES_FREE) free_bytes, sum(BYTES_USED)
used_bytes  
        from V$TEMP_SPACE_HEADER
        group by tablespace_name) c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and c.TABLESPACE_NAME=a.TABLESPACE_NAME
and c.TABLESPACE_NAME=b.TABLESPACE_NAME
order by 1
/
clear computes