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 ds.tablespace_name                               ts_name
      ,ds.owner                                         owner
      ,rtrim(ds.segment_name||’ ‘||ds.partition_name)   segment_name
      ,substr(ds.segment_type,1,7)                      typ
      ,to_char(ds.extents,’999′)                        exts
      ,to_char(ds.initial_extent/1024,’9,999,999′)      initial_K
      ,to_char(ds.next_extent/1024,’9999,999′)          next_k
      ,to_char(ds.bytes/1024,’999,999,999′)             size_k
      ,do.created                                       created
      ,do.last_ddl_time                                 last_ddl_time
from sys.dba_segments ds, sys.dba_objects do
where ds.tablespace_name like upper(nvl(‘&ts_name’,’whoops’)||’%’) and do.object_name = ds.segment_name
order by 1,2,4 desc,3
/
set lines 80
spool off
clear breaks
clear columns