oracle自動化同步兩庫使用者的統計資訊

selectshen發表於2017-05-19
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，可以根據需要定期清理歷史資料。

 
 


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2139545/，如需轉載，請註明出處，否則將追究法律責任。

相關文章