备份恢复统计信息 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-745959/