--上帝为你关闭了一扇门，就一定会为你打开一扇窗。只要你坚持,一定会看到更好更美的风景.
--工作需要,我需要导出一些表的统计信息在另外一台机器做优化.为了以后工作方便做一个记录:
1.生产系统10g:
--建立收集统计的表
exec dbms_stats.create_stat_table('system','tt3','tools');
--说明:tools是我本机的一个表空间
exec dbms_stats.export_table_stats(ownname=>'XXXXX',tabname=>'BQ_XSEDJ',stattab=>'TT3',statown=>'SYSTEM');
--使用exp导出tt3表.忽略.
2.测试环境11g:
--使用imp导入tt3表.我没有使用imp导入,而是直接取出转化为sql语句插入.
SCOTT@40> exec dbms_stats.create_stat_table('SCOTT','tt3','tools');
PL/SQL procedure successfully completed.
--因为我在测试环境的表在scott模式下,必须修改c5='SCOTT'.插入数据.
update tt3 set c5='SCOTT';
commit;
exec dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'BQ_XSEDJ',stattab=>'TT3',statown=>'SCOTT');
--执行过程中报错.
BEGIN dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'BQ_XSEDJ',stattab=>'TT3',statown=>'SCOTT'); END;
Error at line 1
ORA-20002: Version of statistics table SCOTT.TT3 is too old. Please try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: 在 "SYS.DBMS_STATS", line 11211
ORA-06512: 在 "SYS.DBMS_STATS", line 12604
ORA-06512: 在 line 1
SCOTT@40> exec dbms_stats.upgrade_stat_table('scott','TT3');
BEGIN dbms_stats.upgrade_stat_table('scott','TT3'); END;
*
ERROR at line 1:
ORA-01430: column being added already exists in table
ORA-06512: at "SYS.DBMS_STATS", line 6954
ORA-06512: at line 1
--为什么?对比发现仅仅多了最后1列.CLI类型clob.
--删除字段CL1.
SCOTT@40> ALTER TABLE TT3 DROP COLUMN CL1;
Table altered.
SCOTT@40> exec dbms_stats.upgrade_stat_table('scott','TT3');
PL/SQL procedure successfully completed.
再次执行
exec dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'BQ_XSEDJ',stattab=>'TT3',statown=>'SCOTT');
--OK成功.
3.检测:
SCOTT@40> select count(*) from BQ_XSEDJ;
COUNT(*)
----------
0
SCOTT@40> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4gm8yvsg4j1q9, child number 0
-------------------------------------
select count(*) from BQ_XSEDJ
Plan hash value: 398916790
--------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FULL SCAN| PK_BQ_XSEDJ | 6 | 1 (0)|
--------------------------------------------------------------
--可以发现统计信息已经存在!
