统计信息的备份恢复

SQL> create table t1 as select * from dba_objects;
 
Table created
 
SQL> create table t2 as select * from dba_objects;
 
Table created

SQL> select (select count(*) from t1) t1,(select count(*) from t2) t2 from dual;
 
        T1         T2
---------- ----------
     49745      49746

SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------

SQL> exec dbms_stats.gather_table_stats('admin','t1');
 
PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats('admin','t2');
 
PL/SQL procedure successfully completed
 
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------
     49745        704 2012/9/24 10:
     49746        704 2012/9/24 10:

------创建存放统计信息的表t_stat
SQL> conn sys/oracle@remote_2003 as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL> exec dbms_stats.create_t_statle('sys','t_stat');
 
PL/SQL procedure successfully completed
 
SQL> select count(*) from t_stat;
 
  COUNT(*)
----------
         0
------导出统计信息至t_stat表
SQL> exec dbms_stats.export_database_stats('t_stat');
 
PL/SQL procedure successfully completed

SQL> select count(*) from t_stat;
 
  COUNT(*)
----------
     27744

------删除t1,t2统计信息
SQL> conn admin/admin@remote_2003
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as admin
 
SQL> exec dbms_stats.delete_table_stats('admin','t1');
 
PL/SQL procedure successfully completed

SQL> exec dbms_stats.delete_table_stats('admin','t2');
 
PL/SQL procedure successfully completed
 
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------

SQL> conn sys/oracle@remote_2003 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('t_stat');
 
PL/SQL procedure successfully completed

SQL> conn admin/admin@remote_2003
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as admin
 
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
 
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- -------------
     49745        704 2012/9/24 10:
     49746        704 2012/9/24 10:

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26839123/viewspace-745070/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26839123/viewspace-745070/