clear col
set head off
set pause off
set pages 0
set verify off
set feedback off

clear breaks
clear compute

column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,8) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 130 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'Sizing Data - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

set head on
set verify on
set feedback on
col table_name format a30
col ext format 990
col used format 99,990
col empty format 99,990
col used_pct format 990.99
col next format 99,999,990
spool all_sizing.rslt 

set echo on;
/* ******************************************************************* */
/*                                                                     */
/*                >>> Database Review; R.Kupcunas <<<                  */
/*                                                                     */
/*  Name:    all_sizing.sql                                            */
/*  Creator: Rick Kupcunas                                             */
/*  Created: 18-Nov-98                                                 */
/*                                                                     */
/* this script will show various database extent parameters.           */
/*  SECTION 1 - Show all Tables(storage parameters)                    */
/*  SECTION 2 - Show all Indexs(storage parameters)                    */
/*                                                                     */
/* ******************************************************************* */

set echo off;

column TableName    format A18        heading 'Table|Name' 
column IndexName    format A19        heading 'Index|Name' 
column Tablespace   format A9        heading 'Tblspace'
column ObjSize      format 9,999.999  heading 'Total|Size|(MB)' 
column InitialExt   format 999.999    heading 'Initial|Extent|(MB)' 
column NextExt      format 999.999    heading 'Next|Extent|(MB)'  
column InitTrans    format 999        heading 'Init|Trans'
column FreeList     format 999        heading 'Free|List'

set echo on

/* **************************************************** */
/*  SECTION 1 - Show all Tables(space requirements)     */
/* **************************************************** */

set echo off;
break on "TableName" on "IndexName";

select A.Table_name                           			TableName,
       A.tablespace_name                                     Tablespace,
       substr(to_char(B.bytes/(1024*1024), '9,999.999'), 1, 12)  
                                                             ObjSize,
       substr(to_char(A.initial_extent/(1024*1024), '9,999.999'), 1, 10)  
                                                             InitialExt,
       substr(to_char(A.next_extent/(1024*1024), '999.999'), 1, 8)        
                                                             NextExt,
	 substr(to_char(A.ini_trans,'999'),1,4)			InitTrans,
	 substr(to_char(A.freelists,'999'),1,4)			FreeList,
       decode(trunc(A.initial_extent/(1024*1024)),0,' ', '(set INIT=4;FREE=16)') Action
from   dba_tables   A,
       dba_segments B
where A.owner = 'PSOFT'
and B.owner = A.owner
and B.segment_name = A.table_name
and B.segment_type = 'TABLE'
order by 1, 2;

set echo on;


/* **************************************************** */
/*  SECTION 2 - Show all Indexs(space requirements)     */
/* **************************************************** */

set echo off;

select A.Table_name                           			TableName,
       A.Index_name                           			IndexName,
       A.tablespace_name                                     Tablespace,
       substr(to_char(B.bytes/(1024*1024), '9,999.999'), 1, 12)  
                                                             ObjSize,
       substr(to_char(A.initial_extent/(1024*1024), '9,999.999'), 1, 10)  
                                                             InitialExt,
       substr(to_char(A.next_extent/(1024*1024), '999.999'), 1, 8)        
                                                             NextExt,
	 substr(to_char(A.ini_trans,'999'),1,4)			InitTrans,
	 substr(to_char(A.freelists,'999'),1,4)			FreeList,
       decode(trunc(A.initial_extent/(1024*1024)),0,' ', '(set INIT=4;FREE=16)') Action
from   dba_indexes  A,
       dba_segments B
where A.owner = 'PSOFT'
and B.owner = A.owner
and B.segment_name = A.index_name
and B.segment_type = 'INDEX'
order by 1, 2;

clear breaks;
clear compute;
set head on;
set pause off;
spool off;
ttitle off;
commit;
