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.