1. Turn off all jobs and schedulers in target db.
exec dbms_job.broken(1,true)
exec dbms_job.broken(2,true)

exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB')
exec dbms_scheduler.disable('GATHER_STATS_JOB')
exec dbms_scheduler.disable('PURGE_LOG')

2. Export schema statistics of SC shema in PROD db using SYSTEM.STATTAB

#DBMS_STATS.CREATE_STAT_TABLE ('SYSTEM','STATTAB');

select COUNT(*) from SYSTEM.STATTAB;

TRUNCATE TABLE SYSTEM.STATTAB;

exec DBMS_STATS.EXPORT_SCHEMA_STATS ('SC','STATTAB',NULL,'SYSTEM');

select COUNT(*) from SYSTEM.STATTAB;

#select * from DBA_DIRECTORIES;

#expdp \'/ as sysdba\' directory=EXP_DMP dumpfile=exp_stats.dmp logfile=exp_stats.log tables=SYSTEM.STATTAB

exp \'/ as sysdba\' file=exp_stats.dmp log=exp_stats.log tables=SYSTEM.STATTAB

3. Import the statistics into target .

copy the dump file from sc prod server to target server and put it under EXP_DMP directory

select COUNT(*) from SYSTEM.STATTAB;

TRUNCATE TABLE SYSTEM.STATTAB;

#select * from DBA_DIRECTORIES;

#impdp \'/ as sysdba\' directory=EXP_DMP dumpfile=exp_stats.dmp logfile=imp_stats.log tables=SYSTEM.STATTAB

imp \'/ as sysdba\' file=exp_stats_%U.dmp log=imp_stats.log fromuser=system touser=system tables=STATTAB IGNORE=Y

#DROP TABLE SYSTEM.STATTAB;
#imp \'/ as sysdba\' file=exp_stats_%U.dmp log=imp_stats.log FROMUSER=SYSTEM tables=STATTAB

select COUNT(*) from SYSTEM.STATTAB;

exec DBMS_STATS.IMPORT_SCHEMA_STATS ('SC','STATTAB',NULL,'SYSTEM')

#Delete SYSTEM.STATTAB on prod server after confirming that schema stats imported successfully:
#DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','STATTAB')

ITPub博客

一次SCHEMA 统计信息的导入导出