ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空

10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空

原创 Linux操作系统 作者:yangtingkun 时间:2012-07-18 23:19:14 0 删除 编辑

Oracle10gDBA_TAB_STATISTICS视图的STATTYPE_LOCKED列没有正确的显示结果。

 

 

看一个简单的例子:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create table t_part (id number, name varchar2(30))
  2  partition by range (id)
  3  (partition p1 values less than (10),
  4  partition p2 values less than (20),
  5  partition pmax values less than (maxvalue));

Table created.

SQL> select table_name, partition_name, stattype_locked from all_tab_statistics where wner = user and table_name = 'T_PART';

TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T_PART
T_PART                         P1
T_PART                         P2
T_PART                         PMAX

SQL> exec dbms_stats.lock_partition_stats(user, 'T_PART', 'P1')

PL/SQL procedure successfully completed.

SQL> select table_name, partition_name, stattype_locked from all_tab_statistics where wner = user and table_name = 'T_PART';

TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T_PART
T_PART                         P1
T_PART                         P2
T_PART                         PMAX

SQL> exec dbms_stats.gather_table_stats(user, 'T_PART')

PL/SQL procedure successfully completed.

SQL> select table_name, partition_name, last_analyzed, stattype_locked from all_tab_statistics where wner = user and table_name = 'T_PART';

TABLE_NAME                     PARTITION_NAME                 LAST_ANAL STATT
------------------------------ ------------------------------ --------- -----
T_PART                                                        16-JUL-12
T_PART                         P1
T_PART                         P2                             16-JUL-12
T_PART                         PMAX                           16-JUL-12

可以看到在10.2环境中,LOCK_PARTITION_STATS过程是正常工作的,但是DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列并没有正确的显示分区被锁定的结果。

而对于表来说,LOCK_TABLE_STATS过程执行后,STATTYPE_LOCKED的结果显示是正常的:

SQL> exec dbms_stats.lock_table_stats(user, 'T_PART')

PL/SQL procedure successfully completed.

SQL> select table_name, partition_name, last_analyzed, stattype_locked from all_tab_statistics where wner = user and table_name = 'T_PART';

TABLE_NAME                     PARTITION_NAME                 LAST_ANAL STATT
------------------------------ ------------------------------ --------- -----
T_PART                                                        16-JUL-12 ALL
T_PART                         P1                                       ALL
T_PART                         P2                             16-JUL-12 ALL
T_PART                         PMAX                           16-JUL-12 ALL

这说明在10.2中,Oracle对于分区列的锁定的支持是存在问题的。查询了一下MOSOracle将这个问题确认为内部BUG7240460,这个问题在11.1.0.7中被FIXED

而在11.2中,这个问题以及不存在了:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select owner, table_name, partition_name, stattype_locked
  2  from dba_tab_statistics
  3  where wner = 'TEST'
  4  and table_name = 'T_PART';

OWNER      TABLE_NAME   PARTITION_NAME  STATT
---------- ------------ --------------- -----
TEST       T_PART
TEST       T_PART       P2
TEST       T_PART       P3
TEST       T_PART       P4
TEST       T_PART       P5
TEST       T_PART       PMAX

6 rows selected.

SQL> exec dbms_stats.lock_partition_stats('TEST', 'T_PART', 'P2')

PL/SQL procedure successfully completed.

SQL> select owner, table_name, partition_name, stattype_locked
  2  from dba_tab_statistics
  3  where wner = 'TEST'
  4  and table_name = 'T_PART';

OWNER      TABLE_NAME   PARTITION_NAME  STATT
---------- ------------ --------------- -----
TEST       T_PART
TEST       T_PART       P2              ALL
TEST       T_PART       P3
TEST       T_PART       P4
TEST       T_PART       P5
TEST       T_PART       PMAX

6 rows selected.

 

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10354625