isitdevops.com/databases

–tab_all_part_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: ‘
accept partname prompt ‘Name for partition: ‘
col owner         form A8
col table_name    form A14 word wrap
col tabpar_name   form A20 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 9,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 rowcount      form 99,999,999,999
col end_val       form 9,999,999,999,999
col end_act_val   form A20
spool tab_all_part_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 –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’)||’%’)
and partition_name like upper(nvl(‘&&partname’,’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  lb_key
,avg_data_blocks_per_key  db_key
,to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
from dba_indexes
where table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
/
select dip.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
and dip.partition_name like upper(nvl(‘&&partname’,’eric’)||’%’)
order by dbin.index_name,partition_position
/
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 like upper(nvl(‘&&tabname’,’eric’)||’%’)
order by column_name
/
col partition_name   form a12 word wrap
break on partition_name nodup
select– owner
–,table_name
partition_name
,substr(column_name,1,20) col_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_part_col_statistics
where table_name like upper(nvl(‘&&tabname’,’eric’)||’%’)
and partition_name like upper(nvl(‘&&partname’,’eric’)||’%’)
order by partition_name,column_name
/
—
select
column_name
,endpoint_value  end_val
,endpoint_number rowcount
,endpoint_actual_value  end_act_val
from all_tab_histograms
where table_name like upper(nvl(‘&tabname’,’WHOOPS’)||’%’)
order by table_name,column_name,endpoint_number
/
select
partition_name
,substr(column_name,1,20) col_name
,endpoint_value  end_val
,bucket_number rowcount
,endpoint_actual_value  end_act_val
from all_part_histograms
where table_name like upper(nvl(‘&tabname’,’WHOOPS’)||’%’)
and partition_name like upper(nvl(‘&&partname’,’eric’)||’%’)
order by table_name,partition_name,column_name,bucket_number
/
—
clear colu
spool off