可以将Oracle的统计数据从一个数据库迁移到另一个数据库

1 创建统计表

SQL> exec dbms_stats.create_stat_table(ownname=>'NBA',stattab=>'TEST_STAT',tblsp

ace=>'YANG');

PL/SQL 过程已成功完成。

SQL> desc test_stat

 名称                                      是否为空? 类型

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

 STATID                                             VARCHAR2(30)

 TYPE                                               CHAR(1)

 VERSION                                            NUMBER

 FLAGS                                              NUMBER

 C1                                                 VARCHAR2(30)

 C2                                                 VARCHAR2(30)

 C3                                                 VARCHAR2(30)

 C4                                                 VARCHAR2(30)

 C5                                                 VARCHAR2(30)

 N1                                                 NUMBER

 N2                                                 NUMBER

 N3                                                 NUMBER

 N4                                                 NUMBER

 N5                                                 NUMBER

 N6                                                 NUMBER

 N7                                                 NUMBER

 N8                                                 NUMBER

 N9                                                 NUMBER

 N10                                                NUMBER

 N11                                                NUMBER

 N12                                                NUMBER

 D1                                                 DATE

 R1                                                 RAW(32)

 R2                                                 RAW(32)

 CH1                                                VARCHAR2(1000)

自己创建一张表，看看能不能导入

SQL> create table test_stat2 as select * from test_stat;

表已创建。

2 导出统计数据

SQL> exec dbms_stats.export_table_stats(ownname=>'NBA',tabname=>'GAME',stattab=>

'TEST_STAT',cascade=>true,statown=>'NBA');

PL/SQL 过程已成功完成。

查看TEST_STAT表。。。。。。

有了GAME 表的统计数据

试试能否导出到自己创建的表

SQL> exec dbms_stats.export_table_stats(ownname=>'NBA',tabname=>'GAME',stattab=>

'TEST_STAT2',cascade=>true,statown=>'NBA');

PL/SQL 过程已成功完成。

3 迁移统计数据

使用EXP和IMP工具导入和导出统计表

4 导入统计数据

Oracle的优化器不能使用用户表的统计数据,只能使用数据字典的统计数据，所以必须要导入统计数据

SQL> exec dbms_stats.import_table_stats(ownname=>'NBA',tabname=>'GAME',stattab=>

'TEST_STAT',cascade=>true,statown=>'NBA');

PL/SQL 过程已成功完成。