Oracle tablespace size details sql query

Below query can be used to get Oracle database tablespace usage details in MB for resizing informations. Very useful while going through a disk space crunch in database server.

 

select a.TABLESPACE_NAME "TableSpace Name",
round(a.BYTES / 1024 / 1024) "MB Allocated",
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MB Used",
nvl(round(b.BYTES / 1024 / 1024), 0) "MB Free",
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) "Pct Used",
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "Pct Free"
from (select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,
sum(BYTES) BYTES
from sys.dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

SAMPLE OUTPUT

 

select a.tablespace_name, a.block_size, a.initial_extent, a.next_extent, a.extent_management, a.segment_space_management
, b.file_name, b.bytes/1024/1024 "Bytes in MB", b.autoextensible
from dba_tablespaces a, dba_data_files b
where a.tablespace_name = b.tablespace_name;

 

SAMPLE OUTPUT:

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.