Oracle 10g恢複統計資訊的相關函數和過程

上一篇簡單介紹了恢複統計資訊的方法，這一篇介紹相關的視圖和函數。

在上一篇提到了如果要恢複統計資訊，必須提供恢複的TIMESTAMP，而這個時間戳記通過USER_TABLES視圖是無法獲得準確資訊的，因此對於表而言，可以查詢USER_TAB_STATS_HISTORY視圖，而對於SCHEMA和資料庫級的恢復戳可以查詢DBA_OPTSTAT_OPERATIONS視圖：

SQL> SELECT OPERATION,

2  TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') START_TIME,

3  TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') END_TIME

4  FROM DBA_OPTSTAT_OPERATIONS;

OPERATION                      START_TIME                           END_TIME

------------------------------ ------------------------------------ ------------------------------------

gather_database_stats(auto)    2010-09-23 22:00:01.896829 +08:00    2010-09-23 22:00:10.791628 +08:00

gather_database_stats(auto)    2010-09-24 22:00:02.928340 +08:00    2010-09-24 22:00:18.671629 +08:00

gather_database_stats(auto)    2010-09-25 06:00:04.506349 +08:00    2010-09-25 06:00:19.210098 +08:00

gather_database_stats(auto)    2010-09-27 22:00:03.031536 +08:00    2010-09-27 22:00:21.163297 +08:00

gather_database_stats(auto)    2010-09-28 22:00:00.210947 +08:00    2010-09-28 22:00:20.981982 +08:00

gather_database_stats(auto)    2010-09-29 22:00:01.531744 +08:00    2010-09-29 22:00:11.544685 +08:00

gather_database_stats(auto)    2010-10-11 22:00:02.290130 +08:00    2010-10-11 22:00:25.104684 +08:00

gather_database_stats(auto)    2010-10-12 22:00:01.351698 +08:00    2010-10-12 22:00:20.560877 +08:00

gather_database_stats(auto)    2010-10-13 22:00:02.348621 +08:00    2010-10-13 22:00:21.802732 +08:00

gather_database_stats(auto)    2010-10-14 22:00:02.484677 +08:00    2010-10-14 22:00:11.928128 +08:00

gather_database_stats(auto)    2010-10-15 22:00:00.713935 +08:00    2010-10-15 22:00:15.028450 +08:00

gather_database_stats(auto)    2010-10-16 06:00:04.560163 +08:00    2010-10-16 06:00:07.685812 +08:00

gather_database_stats(auto)    2010-10-18 22:00:01.796532 +08:00    2010-10-18 22:00:16.815746 +08:00

gather_database_stats(auto)    2010-10-19 22:00:02.761516 +08:00    2010-10-19 22:00:16.051881 +08:00

gather_database_stats(auto)    2010-10-20 22:00:02.855247 +08:00    2010-10-20 22:00:15.642001 +08:00

gather_database_stats(auto)    2010-10-21 22:00:00.977750 +08:00    2010-10-21 22:00:14.674787 +08:00

gather_database_stats(auto)    2010-10-22 22:00:02.768465 +08:00    2010-10-22 22:00:14.866542 +08:00

gather_database_stats(auto)    2010-10-23 06:00:04.238141 +08:00    2010-10-23 06:00:15.393996 +08:00

18 rows selected.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER)

PL/SQL procedure successfully completed.

SQL> SELECT OPERATION,

2  TARGET,

3  TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') START_TIME,

4  TO_CHAR(END_TIME, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') END_TIME

5  FROM DBA_OPTSTAT_OPERATIONS;

OPERATION                      TARGET START_TIME                           END_TIME

------------------------------ ------ ------------------------------------ ---------------------------------