isitdevops.com/databases

–tab_info
— mdw 30/11/04
— show the stats and info for a table, it’s coulns, it’s indexes…
set veri off pages 64 pause off
accept tabname prompt ‘Name for Table: ‘
col owner         form A8
col ind_owner     form A8
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 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 a15 word wrap
spool tab_info.lst
select owner
,table_name
,num_rows
,blocks
,avg_space   avg_sp
,chain_cnt   chain
,avg_row_len avg_l
,to_char(last_analyzed,’DDMMYY hh24:MI’) lst_anal
,global_stats  gls
,user_stats    uls
from dba_tables
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 like upper(nvl(‘&&tabname’,’eric’))
order by column_id
/
—
col ind_name form a18 wrap
col tab_name form a18 wrap
col col_name form a20 wrap
break on ind_owner nodup on ind_name nodup on tab_name skip 1
 select
 INDEX_OWNER                 ind_owner
,INDEX_NAME                  ind_Name
,TABLE_NAME                  tab_Name
,substr(column_position,1,3) Psn
,COLUMN_NAME                 Col_Name
from all_ind_columns
where table_name like upper(nvl(‘&&tabname’,’WHOOPS’))
order by 3,1,2,4,5
/
—
clear colu
spool off