isitdevops.com/databases

— ts_objs_by_size
— mdw 20/10/97
— summary of contents of a tablespace
set pause on
set pages 24
clear breaks
col segment_name  form a20 word wrap
col ts_name       form a8 word wrap
col owner         form a8 word wrap
break on ts_name skip 1 on owner nodup
spool ts_objs_by_size.lst
select tablespace_name                            ts_name
      ,owner                                      owner
      ,rtrim(segment_name||’ ‘||partition_name)   segment_name
      ,substr(segment_type,1,7)                   typ
      ,to_char(extents,’9,999′)                   extents
      ,to_char(initial_extent/1024,’9,999,999′)   initial_K
      ,to_char(next_extent/1024,’9,999,999′)      next_k
from sys.dba_segments
where tablespace_name like upper(nvl(‘&ts_name’,’whoops’)||’%’) and segment_type like upper(nvl(‘&obj_type’,’TAB’)||’%’)
order by 1, blocks desc, 3
/
spool off
clear breaks
clear columns