/*****************************************
* File:    ssize1.sql ( from segment_size1 )
* Author:  P.Noonan (SGA Consulting Ltd )
******************************************/
col segment_name format a30
col tablespace_name format a15
col type format a10
col owner format a10
col INIT_MB format 9999.99
col NEXT_MB format 9999.99
prompt spool /tmp/segments.lst
rem set termout off
set pagesize 66
set linesize 132
set verify off

REM spool /tmp/segments.lst

select s.segment_name,
       s.segment_type type,
       s.owner,
       s.tablespace_name,
       s.extents,
       s.max_extents,
       round(avg(s.initial_extent) / 1048576,2) INIT_MB,
       round(avg(s.next_extent) / 1048576,2) NEXT_MB,
       round(sum(e.bytes) / 1048576,2) SIZE_MB
from   dba_segments s,
       dba_extents e
where 
       s.segment_name = e.segment_name
  and  s.segment_type = e.segment_type
  and  s.owner = e.owner
  and  s.segment_name like upper('&&1')
group by 
         s.tablespace_name,
         s.segment_name, 
         s.segment_type, 
         s.owner, 
         s.extents,
         s.max_extents
/

select i.table_name, 
       i.index_name, 
       i.initial_extent / 1048576 INIT_MB, 
       i.next_extent / 1048576 NEXT_MB, 
       s.extents,
       i.tablespace_name
  from dba_indexes i, dba_segments s
  where table_name like upper('&&1')
  and  s.segment_name = i.INDEX_NAME
  and  s.segment_type = 'INDEX'
  and  s.owner = i.owner
/

set termout on
REM spool off