过程-自动表分析GATHER_TABLE_STATS

--适用于非分区表
create or replace procedure fx_table as

P_name varchar2(100);
errors varchar2(200);
info2 varchar2(200);
 cursor my_cur is select b.table_name
from dba_tables b
where
and b.OWNER='XXX
and b.PARTITIONED='NO'
order by b.num_rows asc ;

BEGIN
 open my_cur;
 fetch my_cur into P_name ;
 while my_cur%found
   loop
    -- dbms_output.put_line(P_name);
       DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXX,
                                 tabname => P_name,
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree =>4 ,
                                 cascade=>TRUE
                                 );
     insert into fx_table_log values(P_name,sysdate,'');
     commit;
     fetch my_cur into P_name ;
     end loop;
     close my_cur;

EXCEPTION
      WHEN OTHERS THEN
        info2 :=SQLERRM||','||SQLCODE;
        errors:=info2;
 insert into fx_table_log values(P_name,sysdate,errors);
 commit;
end fx_table;