Oracle自动化同步两库用户的统计信息
一些测试需求,需要Oracle同步两库用户的统计信息,例如需要生产环境的统计信息同步到测试库,可以通过dbms_stats下的包export_*/import_*配合dblink来实现。以下举例:DB VERSION:11.2.0.4源库:ct6601目标库:ct6602步骤1:源库,新建用于导出用户统计信息的表begin dbms_stats.create_stat_table(ownname => 'scott', stattab => 'tb_stats', tblspace => 'users');end;步骤2:源库,新建用于导出用户统计信息的存储过程--此处以日期来标记statidcreate or replace procedure scott.p_export_statistics asv_statid varchar2(30) := 'D' || to_char(sysdate, 'yyyymmdd');begin dbms_stats.export_table_stats(ownname => 'scott', tabname => 'dept', stattab => 'tb_stats', statid => v_statid, statown => 'scott');end;步骤3:源库,新建用于导出用户统计信息的JOBdeclare v_job number;begin --具体频率根据需求 sys.dbms_job.submit(job => v_job, what => 'beginscott.p_export_statistics;end;', next_date => to_date('19-05-2017 11:39:50', 'dd-mm-yyyy hh24:mi:ss'), interval => 'trunc(sysdate+1,''dd'')+1/24'); commit;end;步骤4:目标库,新建dblink用于读取源库的scott.tb_stats表create public database link LNK_CT6601 connect to SYSTEM using 'ct6601';步骤5:目标库,新建全局临时表用于临时存过源库的scott.tb_stats表的数据--此处用preserve rows,因为可能要import多个用户的统计信息 CREATE GLOBAL TEMPORARY TABLE scott.tb_temp_stats ON COMMIT preserve rows as select * from SCOTT.tb_stats@LNK_CT6601 where 1=2;步骤6:目标库,新建同步用户统计信息的存储过程--通过statid只同步源库某一天的统计信息过来,并在同步之后锁定统计信息create or replace procedure scott.p_sync_statistics as v_statid varchar2(30) := 'D' || to_char(sysdate, 'yyyymmdd');begin begin insert into scott.tb_temp_stats select * from SCOTT.tb_stats@LNK_CT6601 where statid = v_statid; commit; dbms_stats.import_schema_stats(ownname => 'scott', stattab => 'tb_temp_stats', statid => v_statid, statown => 'scott', force => true); dbms_stats.lock_schema_stats(ownname => 'scott'); end;end; 步骤7:目标库,新建同步用户统计信息的JOB--具体频率根据需求declare v_job number;begin sys.dbms_job.submit(job => v_job, what => 'beginscott.p_sync_statistics;end;', next_date => to_date('19-05-2017 11:39:50', 'dd-mm-yyyy hh24:mi:ss'), interval => 'trunc(sysdate+1,''dd'')+3/24'); commit;end;备注:1.此方案可以用于同步表级、用户级、数据库级的统计信息。2.源库统计信息表scott.tb_stats,可以根据需要定期清理历史数据。
来源: http://www.linuxidc.com/Linux/2017-05/144171.htm