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

362次阅读

共计 2276 个字符，预计需要花费 6 分钟才能阅读完成。

一些测试需求，需要 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;