REM INDICATEUR SUR LA FRAGMENTATION DES ESPACES LIBRES
REM --------------------------------------------------

SET FLUSH OFF
SET TERM OFF
SET LINESIZE 82
SET PAGES 100 
REM
set pages 100
set linesize 82
column tablespace    heading Tablespace format a20
column file_id       heading File       format 99999
column pieces        heading Frag       format 9999
column free_blocks   heading 'Free Blocks'
column largest_blks  heading 'Largest Blocks'
column ratio         heading 'Largest/Free' format 999.99
column HOST_NAME new_value host

select HOST_NAME from v\$INSTANCE;

start title80 "&host    ETAT SUR LA FRAGMENTATION DES ESPACES LIBRES"
define output = './REPORTS/indicateur6.lis'
spool &output
SELECT 
        TABLESPACE_NAME tablespace, 
        FILE_ID, 
        COUNT(*) pieces,
        SUM(BLOCKS) free_blocks,
        MAX(BLOCKS) largest_blks,
        MAX(BLOCKS)/SUM(BLOCKS) ratio
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
spool off
clear columns
SET FLUSH ON
SET TERM ON
exit