oracle复制统计信息

1.DBMS_STATS.EXPORT_SCHEMA

--创建存储统计信息的表
EXEC DBMS_STATS.CREATE_STAT_TABLE('OWNERNAME', 'STATS_EXPORT');

---把OWNERNAME用户的统计信息导入到STATS_EXPORT表
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('OWNERNAME', 'STATS_EXPORT', NULL, 'OWNERNAME');

--更新STATS_EXPORT表，把c5列改为要导入统计信息的用户
update STATS_EXPORT set c5='OWNERNAME1';

---导入之前重新确认信息
select t.last_analyzed,t.stattype_locked,t.* from dba_tab_statistics t where owner='OWNERNAME1';

----导入统计信息到OWNERNAME1用户
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('OWNERNAME1', 'STATS_EXPORT', NULL, 'OWNERNAME');

---导入之后重新确认信息
select t.last_analyzed,t.stattype_locked,t.* from dba_tab_statistics t where owner='OWNERNAME1';

2.expdp方式导入

--导出用户统计信息
expdp "'/ as sysdba'" directory=dump schemas=OWNERNAME dumpfile=expstats_0123_01.dmp include=STATISTICS logfile=expstats0123.log

---确认用户统计信息是否为空
select t.last_analyzed,t.stattype_locked,t.* from dba_tab_statistics t where owner='OWNERNAME1';

---导入用户统计信息
impdp "'/ as sysdba'" directory=dump remap_schema=OWNERNAME:OWNERNAME1 dumpfile=expstats_0123_01.dmp logfile=impstats0123.log

---导入之后重新确认信息
select t.last_analyzed,t.stattype_locked,t.* from dba_tab_statistics t where owner='OWNERNAME1';

----解锁统计信息

EXEC dbms_stats.unlock_schema_stats('OWNERNAME1'); 

----------------------------------------------------

select 'exec dbms_stats.set_table_prefs('''||owner||''','''||table_name||''',''INCREMENTAL'',''TRUE'');'
from (select distinct owner, table_name from DBA_TAB_STATISTICS where stattype_locked IN ('ALL','DATA','CACHE') and owner not in ('SYS','SYSMAN','SYSTEM','WMSYS'));

select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||owner||''',tabname=>'''||table_name||''',method_opt=>''for all columns size auto'',cascade=>true,force=>true,degree=>16);'
from (select distinct owner, table_name from DBA_TAB_STATISTICS where stattype_locked IN ('ALL','DATA','CACHE') and owner not in ('SYS','SYSMAN','SYSTEM','WMSYS'));

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'OWNERNAME',tabname=>'xxxxxxxx',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16,no_invalidate=>false);

exec DBMS_STATS.gather_database_stats(method_opt=>'for all columns size auto',cascade=>true,degree=>16,no_invalidate=>false);

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'OWNERNAME' ,method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16,no_invalidate=>false);

select 'exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'''||username||''', method_opt=>''for all columns size auto'',cascade=>true,force=>true,degree=>16,no_invalidate=>false);'
from dba_users where username in

-- no_invalidate=>false 立即失效