rem ********************************************************************
rem * Description       : Show the analyze statistics of all tables
rem *
rem ********************************************************************

spool all_stat.rslt;

set pagesize 100;
set linesize 132;
break on "OWNER" on "VIEW";

set echo on;

/* ********************* */
/* statistics for tables */
/* ********************* */

set echo off;

select substr(owner,1,8)                                   "OWNER",
       substr(table_name,1,25)                          "TABLE",
       substr(to_char(pct_used, '99'),1,3)              "% USED",
       substr(to_char(pct_free, '99'),1,3)                     "% FREE",
       substr(to_char(num_rows, '99,999'),1,7)         "ROWS",
       substr(to_char(blocks, '99,999'),1,7)             "BLKS",
       substr(to_char(empty_blocks, '99,999'),1,7)      "EMPTY BLKS",
       substr(to_char(avg_space, '99,999'),1,7)             "AVG SPACE",
       substr(to_char(chain_cnt, '9,999'),1,5)              "CHAINING",
       substr(to_char(avg_row_len, '999'),1,4)               "ROW LEN",
       cache                                          "CACHE"
  from sys.all_tables
  where owner <> 'SYS' and
        owner <> 'SYSTEM'
  order by 1, 2;

set echo on;

/* ********************* */
/* statistics for indexes */
/* ********************* */

set echo off;

select substr(owner,1,8)                                     "OWNER",
       substr(table_name,1,25)                               "TABLE",
       substr(index_name,1,25)                               "INDEX",
       decode(uniqueness,'UNIQUE', 'Yes', 'No')              "UNQ?",
       substr(to_char(distinct_keys, '999,999'),1,8)         "#Keys",
       substr(to_char(avg_leaf_blocks_per_key, '999'),1,4)   "Leaf/Key",
       substr(to_char(avg_data_blocks_per_key, '999'),1,4)   "Blks/Key",
       substr(to_char(clustering_factor, '99,999'),1,7)      "Clust Fct"
  from sys.all_indexes
  where owner <> 'SYS' and
        owner <> 'SYSTEM'
  order by 1, 2;

set echo on;
spool off;
commit;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips