— obj_size.sql
— mdw 10/03/97
— show current allocated space for (by default tables) specifed objects
— mdw 24/04/01
— adding in the checking of user to look at – sanger uses multiple schemas

clear break
break on o_type nodup on owner nodup on obj_name nodup skip 1 on tablespace nodup
set veri off
set pause on
set pages 24
set lines 80
col o_type form a8 truncate
col owner form a8 word wrap
col obj_name form a22 word wrap
col tablespace form a14 word wrap
— mdw temp change to spool file
spool obj_siz.lst
select segment_type                                 o_type
      ,owner                                        owner
      ,segment_name||’ ‘||partition_name            obj_name
      ,tablespace_name                              tablespace
      ,substr(extent_id,1,3)                        num
      ,substr(to_char(bytes/1024,’99,999,999′),1,11) K_bytes                 
      ,substr(to_char(blocks,’999,999′),1,8)        blocks
from sys.dba_extents
where segment_name like upper(nvl(‘&nme’,’WHOOPS’)||’%’)
and   segment_type like upper(nvl(‘&typ’,’TABLE’)||’%’)
and   owner like upper(nvl(‘&own’,user)||’%’)
order by 1,2,3,5
clear colu
spool off