spool analyze1.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "OWNER" on "TABLE NAME" on "INDEX NAME";
 
/* ************************************************ */
/*                                                  */
/* substitute the table_name variable with the new  */
/* name                                             */
/*  - will gather statistics about a table and will */
/*    report the number of empty blocks.            */
/*  - will show other statistics about the table    */
/*  - look at the leaf blocks;                      */
/*                                                  */
/* name:    @c:\oracle\sql\analyze1.sql             */
/* date:    16-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

/* ******************* */
/* PERFORM THE ANALYZE */
/* ******************* */

analyze table '&&TABLE_NAME' compute statistics;

select owner			"OWNER",
       empty_blocks
  from sys.dba_tables
  where table_name = upper('&&TABLE_NAME');

/* ******************* */
/* other table parms   */
/* ******************* */

select a.OWNER                         "OWNER",
       a.blocks                        "TTL BLKS ALLOCATED",
       ((a.blocks - b.empty_blocks)/a.blocks) * 100
                                       "%Blocks Used",
       (b.empty_blocks/a.blocks) * 100 "%Blks Empty"
from sys.dba_segments a,
     sys.dba_tables   b
where b.table_name   = upper('&&TABLE_NAME') and
      a.segment_name = b.table_name and
      a.owner        = b.owner;
         
/* ******************* */
/* User table data     */
/* ******************* */

select num_rows		"ROWS",
       blocks    	"BLOCKS",
       empty_blocks     "EMP BLKS",
       avg_space        "AVG SPACE",
       chain_cnt        "CHAINS",
       avg_row_len
  from user_tables
where table_name = upper('&&TABLE_NAME');
         
undefine TABLE_NAME;
set echo off;
spool off;
set long 80;
commit;










