[20120607]restore 舊的統計資訊.txt

今天測試一下,如何restore舊的統計:

select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select rownum id1 ,rownum id2 ,'test' name from dual connect by level<=100 ;
--建立表僅僅100條記錄.
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

2.刪除一些資料,在分析:
SQL> delete from t where id1<=50;
50 rows deleted.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.


3.restore看看.
select status_update_time from user_tab_stats_history;

SQL> select * from user_tab_stats_history where table_name='T';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T                                                                                            2012-06-05 17:35:25.887572
T                                                                                            2012-06-05 17:36:38.553086

SQL> select num_rows from user_tables where table_name='T';
  NUM_ROWS
----------
        50

--可以發現現在的統計僅僅50條.


SQL> exec dbms_stats.restore_table_stats(ownname=>user,tabname=>'T', as_of_timestamp=>' 2012-06-05 17:35:25.887572');

PL/SQL procedure successfully completed.
--注意時間的格式!我定義環境變數:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'

SQL> select num_rows from user_tables where table_name='T';
  NUM_ROWS
----------
       100


SQL> select * from user_tab_stats_history where table_name='T';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T                                                                                            2012-06-05 17:35:25.887572
T                                                                                            2012-06-05 17:36:38.553086
T                                                                                            2012-06-05 17:41:17.564189