abl_idx_size.sql

column mb format 9,999 heading "Mb";
column segment_name format a25 heading "Segment Name";
column segment_type format a8 heading "Seg|Type";
column num_exts format 999 heading "Num|Exts";
column owner format a10 heading "Owner";
column tablespace_name format a20 heading "TableSpace Name";

set pagesize 9999;

break on tablespace_name skip 1
SELECT 
	owner
	, tablespace_name
	, segment_name
	, segment_type
	, sum(bytes)/1048576  mb
	, count(*) num_exts
FROM dba_extents
GROUP BY owner, tablespace_name, segment_name, segment_type
ORDER BY mb desc
/