命令：

数据库的统计信息备份恢复（只能是sys用户下）

exec dbms_stats.create_stat_table('sys','stat_1');

exec dbms_stats.export_database_stats('stat_1');

exec dbms_stats.import_database_stats('stat_1');

方案的统计信息备份恢复（只能在方案拥有者的用户下，niee为用户名）

exec dbms_stats.create_stat_table('niee','stat_2');

exec dbms_stats.export_schema_stats('niee','stat_2');

exec dbms_stats.import_schema_stats('niee','yyy');

表的统计信息备份恢复（只能在表拥有者的用户下）

exec dbms_stats.create_stat_table('niee','stat_3');

exec dbms_stats.export_table_stats('niee','T_HD_ROOM_GROUP_TYPE',null,'stat_3');

exec dbms_stats.import_table_stats('niee','T_HD_ROOM_GROUP_TYPE',null,'stat_3');

实验：思路，先导出database的统计信息，然后删除表的统计信息，然后再导入统计信息，查看表是否还有统计信息

SQL> conn niee/niee@orcl

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as niee

SQL> create table abc as select * from T_CUSTOMER;

Table created

SQL> select table_name,last_analyzed from user_tables where table_name='ABC';

TABLE_NAME                     LAST_ANALYZED

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

ABC                  

SQL> exec dbms_stats.gather_table_stats('NIEE','ABC');

PL/SQL procedure successfully completed

SQL> select table_name,last_analyzed from user_tables where table_name='ABC';

TABLE_NAME                     LAST_ANALYZED

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

ABC                            2011-6-15 16:

SQL> conn sys/oracle@orcl as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

创建存放统计信息的表

SQL> exec dbms_stats.create_stat_table('sys','stats_tab');

PL/SQL procedure successfully completed

导入统计信息

SQL> exec dbms_stats.export_database_stats('stats_tab');

PL/SQL procedure successfully completed

SQL> select count(*) from stats_tab;

  COUNT(*)

----------

    295639

SQL> conn niee/niee@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as niee

删除表ABC的统计信息

SQL> exec dbms_stats.delete_table_stats('NIEE','ABC');

PL/SQL procedure successfully completed

SQL> select table_name,last_analyzed from user_tables where table_name='ABC';

TABLE_NAME                     LAST_ANALYZED

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

ABC            

SQL> conn sys/oracle@orcl as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

导入备份的统计信息

SQL> exec dbms_stats.import_database_stats('stats_tab');

PL/SQL procedure successfully completed

SQL> conn niee/niee@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

Connected as niee

查看一下，ABC的统计信息又有了，证明统计信息导入成功

SQL> select table_name,last_analyzed from user_tables where table_name='ABC';

TABLE_NAME                     LAST_ANALYZED

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

ABC                            2011-6-15 17: