spool all_oco_indexes.rslt 

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

break on "OWNER" on "TABLE";

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

select substr(owner,1,10)                    	"OWNER",
      substr(segment_name,1,15)			"INDEX",
      substr(tablespace_name,1,8)	     	"TSP",
	substr(to_char(bytes/(1024*1024), '9,999.999'),1,10)	"SIZE(Mb)",
	substr(to_char(initial_extent/(1024*1024), '9,999.999'),1, 11)
							"INIT EXT(Mb)",
	substr(to_char(next_extent/(1024*1024), '9,999.999'),1,11)
							"NEXT EXT(Mb)",
	substr(to_char(pct_increase),1,4)		"% INC",
	substr(to_char(extents),1,3)		"EXTS"
from 	dba_segments
where 	segment_name like 'DR%' and
segment_type = 'INDEX'
order by 1 asc, 2 asc, 3 asc;

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









