ORACLE 统计信息的备份与恢复

备份

--需要先创建统计信息基础表
exec dbms_stats.create_stat_table('USER1','STAT_TIMESTAMP');
--导出某个用户的所有统计信息
exec dbms_stats.export_schema_stats('USER1','STAT_TIMESTAMP');

--测试(插入100条，更新统计信息，略)
select num_rows,last_analyzed from dba_tables where table_name='USER1_LOB';
exec dbms_stats.gather_table_stats( 'user1','USER1_LOB');

--查看记录的原先统计信息
col c5 head owner for a25
col c1 head object_name for a25
col d1 head riqi for a22
select type,c5,c1,n1,n2,n3,to_char(d1,'yyyy-mm-dd hh24:mi:ss') d1 from user1.STAT_TIMESTAMP where type='T';

select distinct c1 from STAT_TIMESTAMP where type ='T';

--恢复整个用户的原统计信息
exec dbms_stats.import_schema_stats('USER1','STAT_TIMESTAMP');

execute DBMS_STATS.RESTORE_SCHEMA_STATS('USER1', sysdate-10);
execute DBMS_STATS.RESTORE_SCHEMA_STATS('USER1', TO_DATE('2024-08-02 22:10:12','YYYY-MM-DD HH24:MI:SS'));

恢复方法可多种

execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)