set echo on;
rem ********************************************************************
rem * Author            : Rick Kupcunas
rem *                   : rick@kupcunas.com
rem * Original          : 2004-Nov-10
rem * Description       : Shows the size occupied by an table/index
rem * Usage             : start size_obj.sql;
rem *                   : will prompt for table name 
rem * to run: @C:\rkupcuna05_ora\Oracle\SQL\size_obj.sql
rem *
rem ********************************************************************
set echo off;

col dummy 	noprint format a1;
set pagesize 60;
set linesize 132;
set wrap on;
break on "OWNER" on "TABLE";
col max_ext 			heading 'Max|Exts'       format 9,999
col pct_max 			heading 'Pct of|Max(%)'        format 99.99

spool size_obj.rslt;

set echo on;
/* *************** */
/* Show Table Size */
/* *************** */
set echo off;

select 
     substr(B.tablespace_name,1,12)                             	"TABLESPACE",
     substr(B.owner,1,12)                                       	"OWNER",
     substr(to_char(B.bytes/(1024*1024), '999,999.999'),1,11)   	"MBytes",
     substr(to_char(B.INITIAL_EXTENT/(1024*1024), '9,999.999'),1,9)   	"Init(M)",
     substr(to_char(B.NEXT_EXTENT/(1024*1024), '999,999.999'),1,11)	"Next(M)",
     substr(to_char(B.extents,'9,999'),1,6)                     	"Exts",
     substr(to_char(B.max_extents,'9,999'),1,6)                 	max_ext,
     (B.extents/B.max_extents)*100	  		pct_max,
    decode (sign(75 - CEIL((B.extents/B.max_extents)*100)),1,'n/a',0,'** watch **','* rebuild *') Action
from sys.dba_segments B
where B.segment_name    = upper('&&TABLE_NAME') and
      B.segment_type    = 'TABLE' 
order by 1,2,3;

set echo on;
/* *************** */
/* Show Index Size */
/* *************** */
set echo off;

set wrap on;
break on "TABLESPACE" on "INDEX";

select 
     1 									dummy, 
     substr(B.tablespace_name,1,18)                             	"TABLESPACE",
     substr(B.owner,1,12)                                       	"OWNER",
     substr(A.index_name,1,30)                                  	"INDEX",
     substr(to_char(B.bytes/(1024*1024), '999,999.999'),1,11)   	"MBytes",
     substr(to_char(B.INITIAL_EXTENT/(1024*1024), '9,999.999'),1,9)   	"Init(M)",
     substr(to_char(B.NEXT_EXTENT/(1024*1024), '9,999.999'),1,9)   	"Next(M)",
     substr(to_char(B.extents,'9,999'),1,6)                     	"Exts",
     substr(to_char(B.max_extents,'9,999'),1,6)                 	max_ext,
     (B.extents/B.max_extents)*100	  		pct_max,
     decode (sign(75 - CEIL((B.extents/B.max_extents)*100)),1,'n/a',0,'** watch **','* rebuild *') Action
from  sys.dba_indexes  A,
      sys.dba_segments B
where A.table_name    = upper('&&TABLE_NAME') and
      B.segment_name  = A.index_name          and
      B.segment_type  = 'INDEX'               and
      B.owner         = A.owner
union
select 
     2 									dummy, 
     ' '                 							"TABLESPACE",
     ' '                 							"OWNER",
     ' '                                  				"INDEX",
     '-----------'   							"MBytes",
     '---------'   								"Init(M)",
     '---------'   								"Next(M)",
     '------'                                      	                 	"EXTs",
     '------'                                      	                 	max_ext,
     0                                      	                 	pct_max,
     '-----------'                                      	                 	Action
from  dual
union
select 
     3 									dummy, 
     'ALL TSPS'                 						"TABLESPACE",
     'ALL OWNER'                 						"OWNER",
     'ALL INDEXES'                                  			"INDEX",
     substr(to_char(sum(B.bytes)/(1024*1024), '999,999.999'),1,11)	"MBytes",
     ' '  									"Init(M)",
     ' '  									"Next(M)",
     ' '                                                        	"EXTs",
     ' '                                      	                 	max_ext,
     0                                      	                 	pct_max,
     ' '                                      	                 	Action
from  sys.dba_indexes  A,
      sys.dba_segments B
where A.table_name    = upper('&&TABLE_NAME') and
      B.segment_name  = A.index_name          and
      B.segment_type  = 'INDEX'               and
      B.owner         = A.owner
group by A.table_name
order by 1 asc,
      2 asc, 3 asc;

set echo off;
spool off;
commit;
undefine TABLE_NAME;
