set serveroutput on;

set serveroutput on;

declare f utl_file.file_type;

       cpt number;

       debut number;

       fin number;

BEGIN

cpt:=0;

for i in (select V$FIXED_TABLE.name,type from   fixed_obj$,V$FIXED_TABLE

          where fixed_obj$.obj#=V$FIXED_TABLE.object_id

            and V$FIXED_TABLE.name not in ('X$KSMMEM', 'X$LOGMNR_CONTENTS','X$KZEKMFVW','X$JOXFT','X$JOXFM','X$CDBVW$','X$COMVW$','X$OBLNK$')

         ) loop

          cpt:=cpt+1;

          f:=utl_file.fopen('RTSPV_LOG_DIRECTORY','calcul_stats_01.trc','a');       

           debut:=dbms_utility.get_time();

          utl_file.put_line(f,'Calcul statistiques table #'||to_char(cpt)||' : '||i.name);

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

     ownname          => ''

    ,TabName           => i.name

    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO '

    ,Degree            => NULL

    ,Cascade           => DBMS_STATS.AUTO_CASCADE

    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE

    ,Force             => FALSE);

     

          fin:=dbms_utility.get_time();

         utl_file.put_line(f,'Temps : ' ||to_char(fin-debut)||' s');

          utl_file.fflush(f);

          utl_file.fclose(f);

end loop;

END;

/