ITPub博客

首页 > 数据库 > Oracle > oracle自动化同步两库用户的统计信息

oracle自动化同步两库用户的统计信息

原创 Oracle 作者:selectshen 时间:2017-05-19 13:13:52 0 删除 编辑
    一些测试需求,需要同步两库用户的统计信息,例如需要生产环境的统计信息同步到测试库,可以通过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:源库,新建用于导出用户统计信息的存储过程
--此处以日期来标记statid
create or replace procedure scott.p_export_statistics as
v_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:源库,新建用于导出用户统计信息的JOB
declare
  v_job number;
begin
    --具体频率根据需求
  sys.dbms_job.submit(job       => v_job,
                      what      => 'begin
scott.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      => 'begin
scott.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,可以根据需要定期清理历史数据。

 
 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2139545/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-05

  • 博文量
    169
  • 访问量
    1475535