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 立即失效