备份恢复统计信息

***************************************************************************
生产库上面的操作
sys用户下：
exec dbms_stats.create_stat_table('gc','stat_gc_0825');
exec dbms_stats.export_schema_stats('gc','stat_gc_0825');
***************************************************************************


命令：
数据库的统计信息备份恢复（只能是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');
 

方案的统计信息备份恢复（只能在方案拥有者的用户下）
exec dbms_stats.create_stat_table('nibe','stat_2');
exec dbms_stats.export_schema_stats('GC','GC_STAT');
exec dbms_stats.import_schema_stats('GC','STAT_GC');
 

表的统计信息备份恢复（只能在表拥有者的用户下）
exec dbms_stats.create_stat_table('nibe','stat_3');
exec dbms_stats.export_table_stats('nibe','T_HD_ROOM_GROUP_TYPE',null,'stat_3');
exec dbms_stats.import_table_stats('nibe','T_HD_ROOM_GROUP_TYPE',null,'stat_3');




实验：思路，先导出database的统计信息，然后删除表的统计信息，然后再导入统计信息，查看表是否还有统计信息 
SQL> conn nibe/nibe@orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as nibe
 
SQL> create table abc as select * from T_C_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('NIBE','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 nibe/nibe@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as nibe
 
SQL> exec dbms_stats.delete_table_stats('NIBE','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 nibe/nibe@orcl;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as nibe
 
SQL> select table_name,last_analyzed from user_tables where table_name='ABC';
 
 
TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------
ABC                            2011-6-15 17: