备份恢复统计信息

From:http://space.itpub.net/trackback.php?code=ZwaO01d/ikO5zH2amrlUhjgOJ8A+U14N+EcKm2I

 

命令：

数据库的统计信息备份恢复（只能是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: