ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120607]restore 旧的统计信息.txt

[20120607]restore 旧的统计信息.txt

原创 Linux操作系统 作者:lfree 时间:2012-06-05 17:44:42 0 删除 编辑
今天测试一下,如何restore旧的统计:

select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select rownum id1 ,rownum id2 ,'test' name from dual connect by level<=100 ;
--建立表仅仅100条记录.
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.

2.删除一些数据,在分析:
SQL> delete from t where id1<=50;
50 rows deleted.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.


3.restore看看.
select status_update_time from user_tab_stats_history;

SQL> select * from user_tab_stats_history where table_name='T';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T                                                                                            2012-06-05 17:35:25.887572
T                                                                                            2012-06-05 17:36:38.553086

SQL> select num_rows from user_tables where table_name='T';
  NUM_ROWS
----------
        50

--可以发现现在的统计仅仅50条.


SQL> exec dbms_stats.restore_table_stats(ownname=>user,tabname=>'T', as_of_timestamp=>' 2012-06-05 17:35:25.887572');

PL/SQL procedure successfully completed.
--注意时间的格式!我定义环境变量:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'

SQL> select num_rows from user_tables where table_name='T';
  NUM_ROWS
----------
       100


SQL> select * from user_tab_stats_history where table_name='T';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
T                                                                                            2012-06-05 17:35:25.887572
T                                                                                            2012-06-05 17:36:38.553086
T                                                                                            2012-06-05 17:41:17.564189

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-731981/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2326
  • 访问量
    6056472