isitdevops.com/databases

— ts_conts
— mdw 20/10/97
— summary of contents of a tablespace
set pause on
set pages 24
set lines 90
clear breaks
col segment_name  form a22 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_conts.lst
select tablespace_name                            ts_name
      ,owner                                      owner
      ,rtrim(segment_name||’ ‘||partition_name)   segment_name
      ,substr(segment_type,1,7)                   typ
      ,to_char(extents,’999′)                     exts
      ,to_char(initial_extent/1024,’9,999,999′)   initial_K
      ,to_char(next_extent/1024,’9999,999′)       next_k
     ,to_char(bytes/1024,’999,999,999′)           size_k
from sys.dba_segments
where tablespace_name like upper(nvl(‘&ts_name’,’whoops’)||’%’)
order by 1,2,4 desc,3
/
set lines 80
spool off
clear breaks
clear columns