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

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

Oracle10g的DBA_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>CREATETABLE 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>SELECTTABLE_NAME, partition_name, stattype_locked FROM all_tab_statistics WHERE owner =USERANDTABLE_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/SQLPROCEDURE successfully completed.

SQL>SELECTTABLE_NAME, partition_name, stattype_locked FROM all_tab_statistics WHERE owner =USERANDTABLE_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/SQLPROCEDURE successfully completed.

SQL>SELECTTABLE_NAME, partition_name, last_analyzed, stattype_locked FROM all_tab_statistics WHERE owner =USERANDTABLE_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/SQLPROCEDURE successfully completed.

SQL>SELECTTABLE_NAME, partition_name, last_analyzed, stattype_locked FROM all_tab_statistics WHERE owner =USERANDTABLE_NAME='T_PART';

TABLE_NAME                     PARTITION_NAME                 LAST_ANAL STATT

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

T_PART                                                        16-JUL-12ALL

T_PART                         P1                                       ALL

T_PART                         P2                             16-JUL-12ALL

T_PART                         PMAX                           16-JUL-12ALL

这说明在10.2中，Oracle对于分区列的锁定的支持是存在问题的。查询了一下MOS，Oracle将这个问题确认为内部BUG：7240460，这个问题在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 owner ='TEST'

  4  ANDTABLE_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

6ROWS selected.

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

PL/SQLPROCEDURE successfully completed.

SQL>SELECT owner,TABLE_NAME, partition_name, stattype_locked

  2  FROM dba_tab_statistics

  3  WHERE owner ='TEST'

  4  ANDTABLE_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

6ROWS selected.