drop table MY_TAB_STATS;
CREATE TABLE MY_TAB_STATS(TAB_NUM int identity(1, 1),TAB_NAME VARCHAR2(100),STAT_TIME timestamp(6),STAT_ERR varchar(1000));
–初始化统计信息记录表
truncate table MY_TAB_STATS;
select * from MY_TAB_STATS;
INSERT INTO MY_TAB_STATS(TAB_NAME) select TABLE_NAME from all_tables where OWNER =‘EKP’;
UPDATE MY_TAB_STATS SET STAT_TIME = to_date(‘1970-01-01 00:00:00’);
COMMIT;

create or replace procedure stat_zzj(a int,b int)
as
num_start int;
num_end int;
BEGIN
num_start=a;
num_end=b;
FOR TB IN
(
SELECT TAB_NUM, TAB_NAME FROM MY_TAB_STATS WHERE TAB_NUM >=a and TAB_NUM <=b
)
LOOP
begin
DBMS_STATS.GATHER_TABLE_STATS(‘EKP’, TB.TAB_NAME, NULL, 100, TRUE, ‘FOR ALL COLUMNS SIZE AUTO’);
UPDATE MY_TAB_STATS SET STAT_TIME = sysdate WHERE TAB_NUM = TB.TAB_NUM;
COMMIT;
print TB.TAB_NAME||’ Done,Current Time is ‘|| sysdate;
exception
when others then
UPDATE MY_TAB_STATS SET STAT_TIME=sysdate,STAT_ERR = SQLCODE||’’||SQLERRM WHERE TAB_NUM = TB.TAB_NUM;
end;
END LOOP;
END;

call stat_zzj(0,100);