spool analyze2.rslt 

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

break on "OWNER" on "TABLE NAME" on "INDEX NAME";
 
/* ************************************************ */
/*                                                  */
/*  - will gather statistics of an analyzed table   */
/*    and index                                     */
/*                                                  */
/* name:    @c:\oracle\sql\analyze2.sql             */
/* date:    25-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

/* ******************* */
/* Review Indexes      */
/* ******************* */

select distinct_keys            "KEYS",
       avg_leaf_blocks_per_key  "LEAF BLK",
       avg_data_blocks_per_key  "DATA BLK",
       clustering_factor        "CLUSTER"
  from user_indexes
where table_name = upper('&&TABLE_NAME');
         
/* ******************* */
/* Review Tables       */
/* ******************* */

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;
