isitdevops.com/databases

— show the stats for a partitioned table and it’s indexes… Courtesy of Martin Widlake

set veri off
pages 64
pause off
lines 100
feed off
timi off
accept tabname prompt ‘Name for Table: ‘
col owner         form A8
col table_name    form A14 word wrap
col tabpar_name    form A19 word wrap
col avg_sp        form 9,999
col avg_l         form 9,999
col avg_sp        form 9,999
col chain         form 999 head chn
col clustf        form 9999,999
col lbp_key       form 99,999
col dbp_key       form 99,999
col bl            form 999
col blocks        form 999,999
col num_rows      form 99999,999
col dist_keys     form 99999,999
col l_blks        form 99999,999
col column_name   form a15 word wrap
col index_name    form a15 word wrap
spool tab_par_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’)||’%’)

ORDER BY table_name
/
— now partitions
SELECT –owner
  obj_maint.tab_alias(table_name)||’-‘||partition_name tabpar_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_tab_partitions
WHERE table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
ORDER by table_name,partition_position
/
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  lbp_key, avg_data_blocks_per_key  dbp_key,
  to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
FROM dba_indexes
WHERE table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
ORDER by index_name
/
SELECTdip.partition_name index_name, dip.blevel  bl, dip.leaf_blocks l_blks, dip.distinct_keys dist_keys, 
   dip.clustering_factor        clustf, ,dip.avg_leaf_blocks_per_key  lbp_key, ,dip.avg_data_blocks_per_key  dbp_key,
   to_char(dip.last_analyzed,’DDMMYY hh24:MI:ss’) lst_anal
FROM dba_ind_partitions dip, dba_indexes dbin
WHERE dbin.table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
  AND dbin.index_name = dip.index_name
  AND dbin.owner=dip.index_owner
ORDER BY dbin.index_name, partition_position
/
set pause on timi on feed on
—
clear colu
spool off