Query to get tablespace information from Oracle database - total, used and free space, extents etc.
select a.tablespace_name, round(c.tbytes/1024/1024, 2) as TMB,
round(b.ubytes/1024/1024, 2) as UMB,
round((c.tbytes - b.ubytes)/1024/1024, 2) as FMB,
a.initial_extent, a.next_extent, a.max_extents, a.status, a.extent_management
from
(select tablespace_name, initial_extent, next_extent, max_extents, status, extent_management
from dba_tablespaces) a,
(select tablespace_name, sum(bytes) as ubytes
from dba_segments
group by tablespace_name) b,
(select tablespace_name, sum(bytes) as tbytes
from dba_data_files
group by tablespace_name) c
where b.tablespace_name (+)= a.tablespace_name
and c.tablespace_name (+)= a.tablespace_name
order by fmb asc
/
|