List segments of a tablespace, their properties and sizes

set lines 137
set pages 10000

clear break

col TSname heading 'TSpace|Name|'
col TSname format a25
col SgmntOwner heading 'Sgmnt|Owner|'
col SgmntOwner format a15
col SgmntType heading 'Sgmnt|Type|'
col SgmntType format a15
col SgmntName heading 'Sgmnt|Name|'
col SgmntName format a35
col MinExt heading 'Min|No of|Ext'
col MinExt format 99999999999
col MaxExt heading 'Max|No of|Ext'
col MaxExt format 99999999999
col SgmntSize heading 'Sgmnt|Size|Mb'
col SgmntSize format 9999
col SgmntExentNo heading 'No of|Extent|'
col SgmntExentNo format 9999999999

SELECT
ds.tablespace_name as "TSname",
ds.owner as "SgmntOwner",
ds.segment_type as "SgmntType",
ds.segment_name as "SgmntName",
ds.min_extents as "MinExt",
ds.max_extents as "MaxExt",
ROUND(ds.bytes/1024/1024,0) as "SgmntSize",
SUM(ds.extents) as "SgmntExentNo"
FROM
dba_segments ds
WHERE tablespace_name = 'SYSAUX'
GROUP BY
ds.tablespace_name,
ds.owner,
ds.segment_type,
ds.segment_name,
ds.min_extents,
ds.max_extents,
ds.bytes
ORDER BY
ds.tablespace_name,
ds.owner,
ds.segment_type,
ds.segment_name
;


Happy Learning..!!
Learning..!!