spool all_ndx1.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "OWNER" on "TABLE";

/* ****************************************************** */
/*                                                        */
/* Name:    all_ndx1.sql                                  */
/* Purpose: will show all the indexes for a given database*/
/* Creator: R.Kupcunas                                    */
/* Date:    01-Jan-98                                     */
/*                                                        */
/* ****************************************************** */

select 	substr(a.owner,1,10)                    "OWNER",
        substr(a.table_name,1,15)               "TABLE",
        substr(a.index_name,1,15)		"HOST",
	decode(a.uniqueness, 'UNIQUE', 'Y', 'N')	
						"UNQ",
	substr(to_char(a.initial_extent, '999,999'),1, 8)
						"INIT EXT",
	substr(to_char(a.next_extent, '999,999'),1,8)
						"NEXT EXT",
	substr(to_char(a.pct_increase),1,4)	"% INC",
	substr(to_char(b.extents),1,3)		"EXTS"
from 	all_indexes	a,
	dba_segments	b 
where 	a.owner 	= b.owner    and
	a.index_name 	= b.segment_name
order by 1 asc, 2 asc, 3 asc;

set echo off;
spool off;
set long 80;
commit;









