isitdevops.com/databases

— tab_chain.sql
— mdw 10/07/01
— check for any analysed tables with significant chaining
set pages 24
set pause on
set pause ‘Any Key…’
col table_name form a25 word wrap
col owner      form a15 word wrap
col num_rows   form 999,999,999
col chain_cnt  form 9,999,999
col pct_chn  form 99.99
accept tab_name char prompt ‘which tables are to be checked ? >’
prompt only works on analyzed tables/partitioned tables
spool tab_chain.lst
select table_name
      ,owner
      ,num_rows
      ,chain_cnt
      ,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tables
— NB keep greatest for num_rows and  percentage in line else a table with
— (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1)
     /greatest(nvl(num_rows,1),1000)   > 0.001
and table_name like upper(‘&tab_name’||’%’)
and owner not in (‘SYS’,’SYSTEM’)
union
select table_name||’ ‘||partition_name table_name
      ,table_owner                     owner
      ,num_rows
      ,chain_cnt
      ,(nvl(chain_cnt,1)/nvl(num_rows,1))*100 pct_chn
from sys.dba_tab_partitions
— NB keep greatest for num_rows and  percentage in line else a table with
— (in this case) less than 1000 rows will always be detected.
where greatest(nvl(chain_cnt,1),1)
     /greatest(nvl(num_rows,1),1000)   > 0.001
and table_name like upper(‘&tab_name’||’%’)
and table_owner not in (‘SYS’,’SYSTEM’)
order by 1,2
/
spool off
clear colu