list basic segment details.sql
— seg_par_dets.sql
— mdw 17/09/01
— quick display of basic segment details
set recsep off
col owner    form a8 word wrap
col seg_name form a15 word wrap
col seq_type form a3 head typ
col ts_name  form a8 word wrap
col bytes_k  form 99999,999
col blocks   form 9999,999
col extents  form 9999 head exts
col ini_k    form 999999
col extents  form 999
col nxt_k    form 999999
spool seg_dets.lst
select owner
,segment_name||’ ‘||partition_name seg_name
,decode (segment_type,’TABLE’,’TAB’
                    ,’INDEX’,’IND’
                    ,’TABLE PARTITION’,’TP’
                    ,’INDEX PARTITION’,’IP’
                    ,’ROLLBACK’,’ROL’
                    ,’CLUSTER’,’BLX’
                              ,’OTH’)       seg_type
,tablespace_name                             ts_name
,bytes/1024Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â bytes_k
,blocks
,extents
,initial_extent/1024Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ini_k
,next_extent/1024Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â nxt_k
from dba_segments
where segment_name like nvl(upper(‘&seg_name’),’WHOOPS’)||’%’
and partition_name like nvl(upper(‘&par_name’),’WHOOPS’)||’%’
and owner like upper(‘&owner’)||’%’
order by segment_name||’ ‘||partition_name, owner
/
spool off
clear colu
— EOF
Discussion ¬