spool show_stat_type.rslt 

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

break on "TABLE NAME" on "COLUMN NAME" on "INDEX NAME"; 
 
/* ****************************************************** */
/*                                                        */
/* Name:    show_stat_type.sql                            */
/* Purpose: Determine the type, as well as the date, that */
/*          the statistics were computed                  */
/* Creator: R.Kupcunas                                    */
/* Date:    01-Jan-98                                     */
/*                                                        */
/* ****************************************************** */
       
set echo off;
set feedback off;

select distinct
       substr(TABLE_NAME, 1, 30)     		"TABLE NAME",    
       substr(to_char(LAST_ANALYZED, 'DD-MON-YY HH:MIam'),1,18) "WHEN:",
       'FULL COMPUTE'				"TYPE"
from dba_tab_columns
where table_name = upper('&&TABLE_NAME') and
      num_buckets = 1			 and
      owner not in ('SYS', 'SYSTEM')	 and
      column_name NOT in (select column_name
		from all_ind_columns
		where table_name = upper('&&TABLE_NAME'))
UNION
select distinct
       substr(TABLE_NAME, 1, 30)     		"TABLE NAME",    
       substr(to_char(LAST_ANALYZED, 'DD-MON-YY HH:MIam'),1,18) "WHEN:",
       'HISTOGRAM(tbl)'				"TYPE"
from dba_tab_columns
where table_name = upper('&&TABLE_NAME') and
      num_buckets != 1			 and
      owner not in ('SYS', 'SYSTEM')	 and
      column_name NOT in (select column_name
		from all_ind_columns
	       where table_name = upper('&&TABLE_NAME'))
UNION
select distinct
       substr(TABLE_NAME, 1, 30)     		"TABLE NAME",    
       substr(to_char(LAST_ANALYZED, 'DD-MON-YY HH:MIam'),1,18) "WHEN:",
       'HISTOGRAM(ndx)'				"TYPE"
from dba_tab_columns
where table_name = upper('&&TABLE_NAME') and
      num_buckets != 1			 and
      owner not in ('SYS', 'SYSTEM')	 and
      column_name in (select column_name
		from all_ind_columns
	       where table_name = upper('&&TABLE_NAME'))
UNION
select distinct
       substr(TABLE_NAME, 1, 30)     		"TABLE NAME",    
       substr(to_char(LAST_ANALYZED, 'DD-MON-YY HH:MIam'),1,18) "WHEN:",
       'No Statistics'				"TYPE"
from   dba_tab_columns       
where  table_name = upper('&&TABLE_NAME') and
 	 num_buckets is NULL			and
       owner not in ('SYS', 'SYSTEM')     and
       0 = (select count(*)
	     	from dba_tab_columns
	       where table_name = upper('&&TABLE_NAME') and
                   num_buckets is not NULL)
order by 1 asc,
         2 asc,
         3 asc;
  
spool off;
undefine TABLE_NAME;
set long 80;
commit;
set feedback on;
set echo on;










