isitdevops.com/databases

–tab_stats
— mdw 22/12/04
— show the stats for a table,  it’s indexes…
set veri off pages 64 pause off lines 100
accept tabname prompt ‘Name for Table: ‘
col owner         form A10
col table_name    form A14 word wrap
col avg_sp        form 9,999
col avg_l         form 9,999
col avg_sp        form 9,999
col lb_key        form 99,999
col db_key        form 99,999
col chain         form 9,999
col clustf         form 9999,999
col bl            form 999
col blocks        form 999,999
col low_v         form a10 trunc
col hi_v          form a10 trunc
col num_rows      form 99999,999
col dist_keys     form 99999,999
col l_blks        form 99999,999
col samp_size     form 99999,999
col column_name   form a15 word wrap
col index_name    form a15 word wrap
spool tab_stats.lst
select owner
,table_name
,num_rows
,blocks
,avg_space   avg_sp
,chain_cnt   chain
,avg_row_len avg_l
,global_stats  gls
,user_stats    uls
,to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
,sample_size  samp_size
from dba_tables
where table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
/
select index_name
,substr(index_type,1,3)   typ
,substr(uniqueness,1,3)   unq
,blevel                   bl
,leaf_blocks              l_blks
,distinct_keys            dist_keys
,clustering_factor        clustf
,avg_leaf_blocks_per_key  lb_key
,avg_data_blocks_per_key  db_key
,to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
,sample_size  samp_size
from dba_indexes
where table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
/
set pause on
—
—
clear colu
spool off