恢复统计信息操作也存在一些限制，比如无法恢复用户自定义的统计信息。除此之外由于统计信息是表的属性，因此如果表被删除，则对应的统计备份也被清除，而TRUNCATE操作则并不会影响统计备份信息：

SQL> CREATE TABLE T (ID NUMBER);

Table created.

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;

96 rows created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- --------------
T                                      96          5 25-10月-10

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------------------------
T                              25-10月-10 10.55.10.109647 下午 +08:00

SQL> TRUNCATE TABLE T; 

Table truncated.

SQL> INSERT INTO T SELECT ROWNUM FROM USER_OBJECTS;

242 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T                                     242          5 2010-10-25 22:56:39

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------------------------
T                              25-10月-10 10.55.10.109647 下午 +08:00
T                              25-10月-10 10.56.39.079646 下午 +08:00

SQL> DROP TABLE T PURGE;

Table dropped.

SQL> CREATE TABLE T (ID NUMBER);

Table created.

SQL> INSERT INTO T SELECT ROWNUM FROM TAB;

96 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ------------------------------------------------------------
T                              25-10月-10 10.58.01.280423 下午 +08:00

对于DROP表会清除统计备份信息这很容易理解，还有一点需要注意，就是ANALYZE语句对于统计信息备份的影响：

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T                                      96          5 2010-10-25 23:06:00

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------------------------
T                              25-10月-10 10.58.01.280423 下午 +08:00
T                              25-10月-10 11.06.00.779685 下午 +08:00

SQL> ANALYZE TABLE T COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T                                      96          5 2010-10-25 23:06:50

SQL> SELECT TABLE_NAME, STATS_UPDATE_TIME
  2  FROM USER_TAB_STATS_HISTORY
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -------------------------------------------------------
T                              25-10月-10 10.58.01.280423 下午 +08:00
T                              25-10月-10 11.06.00.779685 下午 +08:00

SQL> BEGIN                             
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  USER,
  4  'T',
  5  TO_TIMESTAMP_TZ('2010-10-25 23:06:00.779685 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T                                      96          5 2010-10-25 23:06:50

SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  USER,
  4  'T',
  5  TO_TIMESTAMP_TZ('2010-10-25 22:58:01.280423 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'));
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME = 'T';

TABLE_NAME                       NUM_ROWS     BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------------
T                                      96          5 2010-10-25 22:58:01

使用ANALYZE语句生成的统计信息是无法进行恢复的，从USER_TAB_STATS_HISTORY视图中也可以看到，里面并不包含ANALYZE语句对应的统计信息。

而ANALYZE语法带来的影响不仅如此，还使得ANALYZE语句执行之前的统计信息无法进行恢复。可以看到，虽然从USER_TAB_STATS_HISTORY视图中可以查询到23:06:00的统计信息，但是利用RESTORE_TABLE_STATS过程无法恢复这个时间点的统计信息。Oracle调用DBMS_STATS包收集统计信息时，在将最新的统计信息写入到数据字典之前，会先将现有的统计信息备份，而ANALYZE语句显然不会进行这个操作，因此导致了ANALYZE执行之前的统计信息丢失。