execute dbms_stats.gather_index_stats('ADABAS_2','ADABAS_2.REFERRAL_DBF_IDX2');

execute dbms_stats.gather_schema_stats('&SCHEMA_OWNER',estimate_percent=>dbms_stats.auto_sample_size, degree=>16);
 
execute dbms_stats.gather_table_stats(ownname=>'&SCHEMA_OWNER',TABNAME=> '&TABLE_NAME',  estimate_percent=>dbms_stats.auto_sample_size, degree=>4);
 

begin
  dbms_stats.gather_schema_stats(ownname=>'REPORTS_DW', estimate_percent=>dbms_stats.auto_sample_size, degree=>6);
  dbms_stats.CREATE_STAT_TABLE( ownname=>'ADIF_OWNER', stattab=>'DW_STATS');
  dbms_stats.export_schema_stats( ownname=>'REPORTS_DW',STATOWN => 'ADIF_OWNER',stattab=>'DW_STATS' , statid=>'CURRENT_STATS');
  execute immediate 'grant select on ADIF_OWNER.DW_STATS to REPORTS_DW';
end;
/
 
set serveroutput on
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
  dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
 --dbms_output.put_line(ObjList(i).ownname);
 IF ObjList(i).ownname in ('&SCHEMA_OWER') THEN
 dbms_output.put_line('Analyze '|| ObjList(i).ObjType ||'  '|| ObjList(i).ownname || '.' || ObjList(i).ObjName || '  ' ||  ObjList(i).partname ||' estimate statistics ;');
 --execute immediate 'Analyze '|| ObjList(i).ObjType ||'  '|| ObjList(i).ownname || '.' || ObjList(i).ObjName || '  ' ||  ObjList(i).partname ||' estimate statistics ;');
 END IF;
END LOOP;
END;
/