FOL 相关 Schema 统计信息迁移

--连接到源库system执行下面脚本
declare
  cursor c_schemas is
    select u.username
      from dba_users u
     where u.username in ('BZARC',
                          'BZBUD',
                          'BZCFM',
                          'BZDSK',
                          'BZFBP',
                          'BZFMT',
                          'BZFPE',
                          'BZGOV',
                          'BZNET',
                          'BZREP',
                          'BZSCF',
                          'BZSIE',
                          'BZFIM',
                          'BZTAX');
begin
  for schm in c_schemas loop
    DBMS_STATS.CREATE_STAT_TABLE(ownname => schm.username, stattab => schm.username || '_STATS');
    DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => schm.username, stattab => schm.username || '_STATS');
  end loop;
end;

--在源库导出统计备份表
expdp system/<SYSTEM口令>@dev DIRECTORY=ODPDIR \
DUMPFILE=expd_zte_stats.dmp LOGFILE=expd_zte_stats.log  \
schemas=BZARC,BZBUD,BZCFM,BZDSK,BZFBP,BZFIM,BZFMT,BZFPE,BZGOV,BZNET,BZREP,BZSCF,BZSIE,BZTAX \
INCLUDE=TABLE:\"LIKE \( \'BZ%_STATS\' \)\"

–在目标库导入统计备份表
impdp system/@sit DIRECTORY=EBS_TEMP DUMPFILE=expd_zte_stats.dmp LOGFILE=expd_zte_stats.log

--连接到目标库system执行下面脚本
declare
  cursor c_schemas is
    select u.username
      from dba_users u
     where u.username in ('BZARC',
                          'BZBUD',
                          'BZCFM',
                          'BZDSK',
                          'BZFBP',
                          'BZFMT',
                          'BZFPE',
                          'BZGOV',
                          'BZNET',
                          'BZREP',
                          'BZSCF',
                          'BZSIE',
                          'BZFIM',
                          'BZTAX');
begin
  for schm in c_schemas loop
    begin 
      DBMS_STATS.upgrade_stat_table(ownname => schm.username,stattab => schm.username || '_STATS');
    exception when others then
      null;
    end;
    DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => schm.username, stattab => schm.username || '_STATS');
  end loop;
end;