一次SCHEMA 统计信息的导入导出_ITPUB博客

来自 : ITPUB 发布时间：2021-03-24

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 ofSC 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.STATTAB3.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.STATTABimp \\\'/ 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=STATTABselect 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博客 ” ，链接：http://blog.itpub.net/11976525/viewspace-665629/，如需转载，请注明出处，否则将追究法律责任。