isitdevops.com/databases

–tab_all_stats
— mdw 30/11/04
— show the stats for a table, it’s coulns, it’s indexes…
set veri off pages 64 pause off lines 100
accept tabname prompt ‘Name for Table: ‘
col owner         form A8
col table_name    form A14 word wrap
col avg_sp        form 9,999
col avg_l         form 9999
col avg_sp        form 9,999
col n_buck        form 9,999
col chain         form 9,999
col clustf         form 999,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 num_nulls     form 99,999
col column_name   form a20 word wrap
col index_name    form a15 word wrap
col buckno        form 99,999,999,999
col end_val       form 9,999,999,999,999
col end_act_val   form A10
spool tab_all_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 = upper(nvl(‘&&tabname’,’eric’))
–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
from dba_indexes
where table_name = upper(nvl(‘&&tabname’,’eric’))
–where table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
/
set pause on
—
select– owner
–,table_name
column_name
,num_distinct
,low_value    low_v
,high_value   hi_v
,num_nulls    n_nulls
,num_buckets  n_buck
,avg_col_len  avg_l
from dba_tab_columns
where table_name = upper(nvl(‘&&tabname’,’eric’))
–where table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
order by column_id
/
select
column_name
,endpoint_value  end_val
,endpoint_number buckno
,endpoint_actual_value  end_act_val
from all_tab_histograms
where table_name = upper(nvl(‘&tabname’,’WHOOPS’))
–where table_name like upper(nvl(‘&tabname’,’WHOOPS’)||’%’)
order by table_name,column_name,endpoint_number
/
—
clear colu
spool off