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> 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 owner = 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 owner = 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 owner = 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 owner = 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对于分区列的锁定的支持是存在问题的。查询了一下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 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 owner = '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. |