这边环境有很多月表，都是之前很早就创建好的，每到出帐的时候，就会因为没有统计信息导致业务运行慢，重新进行表分析后，正常，为了预防再次出现，考虑将上个月的月表的统计信息手动迁移到这个月。环境中是按地市来创建的schema，所以同一个用户下面会存在相同的表。下面是在自己的测试环境中测试：
os:win7 db:11.2.0.3 schema:htz,scott table_name:dbms_test,dbms_test1;
htz.scott.下面分别存在dbms_test,dbms_text1两张表，dbms_text表上面有统计信息，需要把dbms_text上面的统计信息迁移到dbms_text1上面。
1，创建测试表
1.1 创建测试表 CREATE TABLE SCOTT.DBMS_TEST AS SELECT * FROM DBA_OBJECTS; CREATE TABLE SCOTT.DBMS_TEST1 AS SELECT * FROM DBA_OBJECTS; CREATE TABLE HTZ.DBMS_TEST AS SELECT * FROM DBA_OBJECTS; CREATE TABLE HTZ.DBMS_TEST1 AS SELECT * FROM DBA_OBJECTS; 1.2 创建索引 CREATE INDEX SCOTT.IND_DBMS_TEST_OWNER ON SCOTT.DBMS_TEST(OWNER); CREATE INDEX HTZ.IND_DBMS_TEST_OWNER ON SCOTT.DBMS_TEST(OWNER); CREATE INDEX SCOTT.IND_DBMS_TEST1_OWNER ON SCOTT.DBMS_TEST1(OWNER); CREATE INDEX HTZ.IND_DBMS_TEST1_OWNER ON SCOTT.DBMS_TEST1(OWNER); 1.3 收集dbms_text表的统计信息 exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'DBMS_TEST',cascade=>true); exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'DBMS_TEST',cascade=>true);
2，创建STAT_TABLE_MAPPING表
用于存放对象mapping关系的
CREATE TABLE SYSTEM.STAT_TABLE_MAPPING ( OWNER VARCHAR2(30 BYTE), TYPE VARCHAR2(20 BYTE), SOURCE VARCHAR2(100 BYTE), TARGET VARCHAR2(100 BYTE) ) alter table system.stat_table_mapping add constraint pk_stat_table_mapping primary key(owner,source);
3，插入测试表的对应关系
insert into SYSTEM.stat_table_mapping values('scott','t','dbms_test','dbms_test1'); insert into SYSTEM.stat_table_mapping values('htz','t','dbms_test','dbms_test1'); insert into SYSTEM.stat_table_mapping values('scott','i','ind_dbms_test_owner','ind_dbms_test1_owner'); insert into SYSTEM.stat_table_mapping values('htz','i','ind_dbms_test_owner','ind_dbms_test1_owner');
4，创建stat_table用于存放统计信息
exec DBMS_STATS.CREATE_STAT_TABLE(ownname=>'SYSTEM', STATTAB=>'DBMS_STAT_TABLE');
5，创建存储过程，用于迁移统计信息
/* Formatted on 2013/9/26 22:05:50 (QP5 v5.240.12305.39476) */ CREATE OR REPLACE PROCEDURE import_dbms_stats AS v_source VARCHAR2 (30); v_target VARCHAR2 (30); v_username VARCHAR2 (30); BEGIN FOR cuser IN (SELECT DISTINCT owner FROM SYSTEM.stat_table_mapping WHERE UPPER (TYPE) = 'T') LOOP v_username := UPPER (cuser.owner); DBMS_OUTPUT.put_line ('v_username:' || v_username); FOR ctable IN (SELECT source, target FROM SYSTEM.stat_table_mapping WHERE UPPER (TYPE) = 'T' AND UPPER (owner) = v_username) LOOP v_source := UPPER (ctable.source); v_target := UPPER (ctable.target); DBMS_OUTPUT.put_line ('v_source ' || v_source); DBMS_OUTPUT.put_line ('v_target ' || v_target); EXECUTE IMMEDIATE 'truncate table system.dbms_stat_table'; DBMS_STATS.export_table_stats (ownname => v_username, tabname => v_source, stattab => 'DBMS_STAT_TABLE', cascade => TRUE, statown => 'system'); DBMS_OUTPUT.put_line ('EXPORT_TABLE_STATS ' || v_source); UPDATE SYSTEM.DBMS_STAT_TABLE a SET a.c1 = (SELECT UPPER (b.target) FROM SYSTEM.stat_table_mapping b WHERE a.c1 = UPPER (b.source) AND a.c5 = UPPER (b.owner) AND a.c5 = v_username); COMMIT; DBMS_OUTPUT.put_line ( 'update dbms_stat_table :' || v_source || ' success'); DBMS_STATS.import_table_stats (ownname => v_username, tabname => v_target, stattab => 'DBMS_STAT_TABLE', statown => 'system'); DBMS_OUTPUT.put_line ( 'import dbms_stat_table :' || v_target || ' success'); END LOOP; END LOOP; END;
6，测试迁移过程
6.1 手动删除表，列，索引相关的统计信息 SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'DBMS_TEST1', CASCADE_PARTS=>true, CASCADE_COLUMNS=>true, CASCADE_INDEXES=>true); SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'HTZ', TABNAME=>'DBMS_TEST1', CASCADE_PARTS=>true, CASCADE_COLUMNS=>true, CASCADE_INDEXES=>true); 确认相当的统计信息已经删除 SQL> SELECT owner, 2 table_name, 3 TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed 4 FROM dba_tables a 5 WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST'; SCOTT DBMS_TEST 2013-09-26 21:28:36 SCOTT DBMS_TEST1 HTZ DBMS_TEST 2013-09-26 21:22:00 HTZ DBMS_TEST1 SQL> SELECT owner, 2 index_name, 3 TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed 4 FROM dba_indexes a 5 WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST'; HTZ IND_DBMS_TEST_OWNER 2013-09-26 21:22:00 HTZ IND_DBMS_TEST1_OWNER SCOTT IND_DBMS_TEST_OWNER 2013-09-26 21:28:36 SCOTT IND_DBMS_TEST1_OWNER SQL> 6.2 执行过程迁移 SQL> exec import_dbms_stats; v_username:HTZ v_source DBMS_TEST v_target DBMS_TEST1 EXPORT_TABLE_STATS DBMS_TEST update dbms_stat_table :DBMS_TEST success import dbms_stat_table :DBMS_TEST1 success v_username:SCOTT v_source DBMS_TEST v_target DBMS_TEST1 EXPORT_TABLE_STATS DBMS_TEST update dbms_stat_table :DBMS_TEST success import dbms_stat_table :DBMS_TEST1 success 6.3 查看迁移是否成功 SQL> set lines 200 SQL> SELECT owner, 2 table_name, 3 TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed 4 FROM dba_tables a 5 WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST'; OWNER TABLE_NAME LAST_ANALYZED ------------------------------ ------------------------------ ------------------- SCOTT DBMS_TEST 2013-09-26 21:28:36 SCOTT DBMS_TEST1 2013-09-26 21:28:36 HTZ DBMS_TEST 2013-09-26 21:22:00 HTZ DBMS_TEST1 2013-09-26 21:22:00 SQL> SELECT owner, 2 index_name, 3 TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed 4 FROM dba_indexes a 5 WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST'; OWNER INDEX_NAME LAST_ANALYZED ------------------------------ ------------------------------ ------------------- HTZ IND_DBMS_TEST_OWNER 2013-09-26 21:22:00 HTZ IND_DBMS_TEST1_OWNER 2013-09-26 21:22:00 SCOTT IND_DBMS_TEST_OWNER 2013-09-26 21:28:36 SCOTT IND_DBMS_TEST1_OWNER 2013-09-26 21:28:36
这里我们可以发现相当的统计信息已经有了，说明迁移成功。
ORACLE 迁移表、列、索引的统计信息到另一张结构完全相同的表：等您坐沙发呢！