oracle数据库同时多人备库,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：源库，新建用于导出用户统计信息的存储过程

--此处以日期来标记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，可以根据需要定期清理历史数据。