--
--	tblidx.sql
--

set heading on
set pagesize 66
set linesize 500

ttitle 'Index Information'
column tab_name format a13 heading 'Table'
column num_rows format 9999999 heading '# Rows'
column ind_name format a20 heading 'Index'
column tablespace_name format a10 heading 'Table|Space'
column uniqueness format a1 heading 'U'
column col_name format a12 heading 'Column'
column pct_free  format  99 heading 'Pct|Free'
column clustering_factor format  99990 heading 'Cluster|Factor'
column blevel format  99 heading 'BLevel'
column leaf_blocks format  9999 heading 'Leaf'
column num_distinct format 9999999 heading '# Dist'
column Balance  format 990
break on tab_name skip 1 on ind_name on uniqueness on num_rows

spool tblidx.lst

select 	x.table_name tab_name,
	x.num_rows num_rows,
	a.index_name ind_name,
            a.tablespace_name,
	decode(a.uniqueness, 'UNIQUE', 'U', 'N') uniqueness,
	b.column_name col_name,
	y.num_distinct num_distinct,
           a.pct_free,
           a.clustering_factor,
           a.blevel,
           a.leaf_blocks,
           (c.del_lf_rows_len / c.lf_rows_len) * 100  as  Balance
from	dba_tables x, dba_tab_columns y, dba_indexes a, dba_ind_columns b, index_stats c
where	x.owner  = 'SCOTT'
and	a.table_owner = x.owner
and	a.table_owner = b.index_owner
and	x.table_name = a.table_name
and	a.table_name = b.table_name
and	a.index_name = b.index_name
and	x.owner = y.owner
and	x.table_name = y.table_name
and	b.column_name = y.column_name
and      b.index_name = c.name(+)
order   by  x.table_name 
/
spool off

