-- space_ts.sql
--
-- Tablespaces - Show allocated and used space for each tablespace in the database
--
-- @space_ts.sql
--
column UsedMegs format 99999.999;
column AllocatedMegs format 99999.999;
select b.tablespace_name,nvl(a.UsedMegs,0) as UsedMegs,b.AllocatedMegs as AllocatedMegs
from 
(select tablespace_name,round(sum(bytes)/1048576,3)||'M' as UsedMegs from dba_segments group by tablespace_name) a,
(select tablespace_name,round(sum(bytes)/1048576,3)||'M' as AllocatedMegs from dba_data_files group by tablespace_name) b
where b.tablespace_name = a.tablespace_name(+)
/
select a.UsedMegs, b.AllocatedMegs 
from 
(select round(sum(bytes)/1048576,3)||'M' as UsedMegs from dba_segments) a,
(select round(sum(bytes)/1048576,3)||'M' as AllocatedMegs from dba_data_files) b
/