Oracle统计信息的导出导入测试示例详解

--源端统计信息导出：
begin
 DBMS_STATS.CREATE_STAT_TABLE('SYSTEM','JINGYU_STATS_20181217'); 
 DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'JINGYU', STATTAB => 'JINGYU_STATS_20181217', STATOWN => 'SYSTEM');
end;
/
expdp \'/ as sysdba\' directory=xtts dumpfile=stats_jingyu.dmp logfile=stats_jingyu.log tables=system.jingyu_stats_20181217

--目标端统计信息导入：
impdp \'/ as sysdba\' directory=xtts dumpfile=stats_jingyu.dmp logfile=stats_jingyu.log cluster=n
exec DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME => 'JINGYU', STATTAB => 'JINGYU_STATS_20181217', STATOWN => 'SYSTEM');

--删除存放统计信息的表（根据实际需要选择性执行）：
exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','JINGYU_STATS_20181217');

--源端统计信息导出：
begin
 DBMS_STATS.CREATE_STAT_TABLE('SYSTEM','DB_STATS_20181217'); 
 DBMS_STATS.EXPORT_DATABASE_STATS(STATTAB => 'DB_STATS_20181217', STATOWN => 'SYSTEM');
end;
/
expdp \'/ as sysdba\' directory=xtts dumpfile=stats.dmp logfile=stats.log tables=system.db_stats_20181217

--目标端统计信息导入：
impdp \'/ as sysdba\' directory=xtts dumpfile=stats.dmp logfile=stats.log cluster=n
exec DBMS_STATS.IMPORT_DATABASE_STATS (STATTAB => 'DB_STATS_20181217', STATOWN => 'SYSTEM');

--删除存放统计信息的表（根据实际需要选择性执行）：
exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','DB_STATS_20181217');

SYS@orcl> select owner, table_name, NUM_ROWS from dba_tables where owner = 'JINGYU';

OWNER       TABLE_NAME      NUM_ROWS
------------------------------ ------------------------------ ----------
JINGYU       TEST        100708
JINGYU       ASH_TMP        226

SYS@orcl> insert into jingyu.ash_tmp select * from jingyu.ash_tmp;
SYS@orcl> commit;

SYS@orcl> exec dbms_stats.gather_table_stats('JINGYU','ASH_TMP');

PL/SQL procedure successfully completed.

SYS@orcl> select owner, table_name, num_rows from dba_tables where owner='JINGYU';

OWNER       TABLE_NAME      NUM_ROWS
------------------------------ ------------------------------ ----------
JINGYU       TEST        100708
JINGYU       ASH_TMP        452

SYS@orcl> select owner, table_name, num_rows from dba_tables where owner='JINGYU';

OWNER       TABLE_NAME      NUM_ROWS
------------------------------ ------------------------------ ----------
JINGYU       TEST        100708
JINGYU       ASH_TMP        226

SYS@orcl> 

exec dbms_stats.upgrade_stat_table('SYSTEM','db_stats_20181217');