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