Oracle 10g恢複統計資訊的限制

恢複統計資訊操作也存在一些限制，比如無法恢複使用者自訂的統計資訊。除此之外由於統計資訊是表的屬性，因此如果表被刪除，則對應的統計備份也被清除，而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月-1010.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月-1010.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