備份恢復統計資訊 backup and restore stats

以前的一篇備份恢復統計資訊的測試示例，希望對大家有用。
備份恢復表統計資訊
SQL> desc test.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)

檢視錶分析時間
SQL> select to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') from dba_tables where table_name='TEST' AND WNER='TEST';

TO_CHAR(LAST_ANALYZ
-------------------
2012/08/21 00:32:23

--檢視當前時間
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012/09/28 08:21:58

建立統計資訊儲存表，收集表統計資訊並將統計資訊儲存到儲存表
SQL> BEGIN
  2     DBMS_STATS.CREATE_STAT_TABLE ('test', 'savestats');
  3     DBMS_STATS.GATHER_TABLE_STATS ('test', 'test', stattab => 'savestats');
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-20002: Unable to create table SAVESTATS: already exists
ORA-06512: at "SYS.DBMS_STATS", line 7060
ORA-06512: at line 2

刪除統計資訊儲存表
SQL> BEGIN
  2     DBMS_STATS.DROP_STAT_TABLE ('test', 'savestats');
  3  END;
  4  /

PL/SQL procedure successfully completed.

建立統計資訊儲存表，收集表統計資訊並將統計資訊儲存到儲存表
SQL> BEGIN
  2     DBMS_STATS.CREATE_STAT_TABLE ('test', 'savestats');
  3     DBMS_STATS.GATHER_TABLE_STATS ('test', 'test', stattab => 'savestats');
  4  END;
  5  /

PL/SQL procedure successfully completed.

檢視錶分析時間
SQL> select to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') from dba_tables where table_name='TEST' AND WNER='TEST';

TO_CHAR(LAST_ANALYZ
-------------------
2012/09/28 08:25:33

檢視當前時間
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2012/09/28 08:26:20

刪除當前表統計資訊，將表舊統計資訊從儲存表還原
SQL> BEGIN
  2     DBMS_STATS.DELETE_TABLE_STATS ('test', 'test');
  3     DBMS_STATS.IMPORT_TABLE_STATS ('test', 'test', stattab => 'savestats');
  4  END;
  5  /

PL/SQL procedure successfully completed.

檢視錶分析時間
SQL> select to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss') from dba_tables where table_name='TEST' AND WNER='TEST';

TO_CHAR(LAST_ANALYZ
-------------------
2012/08/21 00:32:23

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-745959/，如需轉載，請註明出處，否則將追究法律責任。