isitdevops.com/databases

— par_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 par_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   partition_name like upper(‘&ptn’||’%’)
and   owner like upper(nvl(‘&own’,user)||’%’)
order by 1,2,3,5
/
clear colu
spool off