set verify off
set COLSEP "|"
set UNDERLINE "="
set NULL "........ NoStats ...... " TRUNC on
set trimspool on
set PAGES 999
col LATABLE for A15 HEAD "Table" JUSTIFY C
col TAILLE for 999,999,999,999 HEAD "Taille du segment|en bytes" JUSTIFY C
col GAIN_REORG_1 for 999,999,999,999 HEAD "1ere resconstruction|-|espace libere|en bytes" JUSTIFY C
col GAIN_REORG_2 for 999,999,999,999 HEAD "2eme resconstruction|-|espace libere|en bytes" JUSTIFY C
col TBS for A15  HEAD "Tablespace" JUSTIFY C
col DATEXE for A6  HEAD "Date|du|jour" JUSTIFY C
col DATANA for A6  HEAD "Date|des|stats" JUSTIFY C
col AVG for 999,999  HEAD "espace|moyen|libre|par blk|en bytes" JUSTIFY C
col VIDE for 999,999 HEAD "Blocs|vides|apres|le HWM" JUSTIFY C
col PFREE for A4 HEAD "Pct|free|%" JUSTIFY C
set linesize 150
set feed  off

col C11 new_value NOMBASE
col C12 new_value TIMESTAMPE

set head off
select name C11 from v$database 
/
Select to_char(sysdate,'DDMMYYYYHHMI') C12 from v$database 
/
clear screen
set head on
prompt Traitement en cours ....
spool /tmp/need_reorg/&&NOMBASE._&&TIMESTAMPE

TTITLE "Evaluation de l espace disque qui pourrait etre recycle | dans l'hypothese de deux reconstructions successives"
break on TBS REPORT dup skip 1
compute sum LABEL 'Total par Tbs:' of GAIN_REORG_2 on TBS
compute sum LABEL 'Total par Tbs:' of GAIN_REORG_1 on TBS
compute sum LABEL 'Total pour tous les Tablespaces:' of GAIN_REORG_2 on REPORT
compute sum LABEL 'Total pour tous les Tablespaces:' of GAIN_REORG_1 on REPORT
select (select to_char(sysdate,'DDMMYY') from dual) DATEXE,
        to_char(t.last_analyzed,'DDMMYY') DATANA,
        t.tablespace_name TBS,segment_name LATABLE,
        s.bytes TAILLE,
        ( t.empty_blocks * (select value from v$parameter where name  = 'db_block_size' )) GAIN_REORG_1,
        ( t.empty_blocks * (select value from v$parameter where name = 'db_block_size'))+((t.avg_space*t.blocks)*((100-t.pct_free)/100)) GAIN_REORG_2,
        t.avg_space AVG,
        t.empty_blocks VIDE,
        t.pct_free||'%' PFREE
from     dba_tables t, dba_segments s
where    table_name = segment_name 
and      s.bytes > 500000000 order by TBS , GAIN_REORG_2 DESC, AVG  
/

spool off

prompt                         ===========================================================
prompt Resultat sous :         /tmp/need_reorg/&&NOMBASE._&&TIMESTAMPE 
prompt                         ===========================================================
exit
