is there a better way of writing this SQL

[stecal]

You could try this:

create table extent_details as
select count(segment_name) seg_count, sum(extents) sum_ext, segment_name
from dba_segments
group by segment_name
order by 2 desc

SQL> select * from extent_details;


SEG_COUNT SUM_EXT SEGMENT_NAME
---------- ---------- ---------------
1 79 SOURCE$
1 39 I_SOURCE1
1 28 SYSROL
1 22 IDL_UB1$
1 21 IDL_UB2$
12 12 CARS
1 12 VIEW$
1 9 ARGUMENT$
1 9 C_OBJ#
1 8 I_COL1
1 8 SYSTEM