Oracle10g\u7684DBA_TAB_STATISTICS\u89c6\u56fe\u7684STATTYPE_LOCKED\u5217\u6ca1\u6709\u6b63\u786e\u7684\u663e\u793a\u7ed3\u679c\u3002
\n\u770b\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\uff1a<\/p>\n
SQL> select * from v$version;\r\nBANNER\r\n----------------------------------------------------------------\r\nOracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi\r\nPL\/SQL Release 10.2.0.5.0 - Production\r\nCORE 10.2.0.5.0 Production\r\nTNS for Linux: Version 10.2.0.5.0 - Production\r\nNLSRTL Version 10.2.0.5.0 - Production\r\nSQL> create table t_part (id number, name varchar2(30))\r\n 2 partition by range (id)\r\n 3 (partition p1 values less than (10), \r\n 4 partition p2 values less than (20), \r\n 5 partition pmax values less than (maxvalue));\r\nTable created.\r\nSQL> select table_name, partition_name, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART';\r\nTABLE_NAME PARTITION_NAME STATT\r\n------------------------------ ------------------------------ -----\r\nT_PART\r\nT_PART P1\r\nT_PART P2\r\nT_PART PMAX\r\nSQL> exec dbms_stats.lock_partition_stats(user, 'T_PART', 'P1')\r\nPL\/SQL procedure successfully completed.\r\nSQL> select table_name, partition_name, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART';\r\nTABLE_NAME PARTITION_NAME STATT\r\n------------------------------ ------------------------------ -----\r\nT_PART\r\nT_PART P1\r\nT_PART P2\r\nT_PART PMAX\r\nSQL> exec dbms_stats.gather_table_stats(user, 'T_PART')\r\nPL\/SQL procedure successfully completed.\r\nSQL> select table_name, partition_name, last_analyzed, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART';\r\nTABLE_NAME PARTITION_NAME LAST_ANAL STATT\r\n------------------------------ ------------------------------ --------- -----\r\nT_PART 16-JUL-12\r\nT_PART P1\r\nT_PART P2 16-JUL-12\r\nT_PART PMAX 16-JUL-12<\/pre>\n\u53ef\u4ee5\u770b\u5230\u572810.2\u73af\u5883\u4e2d\uff0cLOCK_PARTITION_STATS\u8fc7\u7a0b\u662f\u6b63\u5e38\u5de5\u4f5c\u7684\uff0c\u4f46\u662fDBA_TAB_STATISTICS\u89c6\u56fe\u7684STATTYPE_LOCKED\u5217\u5e76\u6ca1\u6709\u6b63\u786e\u7684\u663e\u793a\u5206\u533a\u88ab\u9501\u5b9a\u7684\u7ed3\u679c\u3002
\n\u800c\u5bf9\u4e8e\u8868\u6765\u8bf4\uff0cLOCK_TABLE_STATS\u8fc7\u7a0b\u6267\u884c\u540e\uff0cSTATTYPE_LOCKED\u7684\u7ed3\u679c\u663e\u793a\u662f\u6b63\u5e38\u7684\uff1a<\/p>\n
SQL> exec dbms_stats.lock_table_stats(user, 'T_PART') \r\nPL\/SQL procedure successfully completed.\r\nSQL> select table_name, partition_name, last_analyzed, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART';\r\nTABLE_NAME PARTITION_NAME LAST_ANAL STATT\r\n------------------------------ ------------------------------ --------- -----\r\nT_PART 16-JUL-12 ALL\r\nT_PART P1 ALL\r\nT_PART P2 16-JUL-12 ALL\r\nT_PART PMAX 16-JUL-12 ALL<\/pre>\n\u8fd9\u8bf4\u660e\u572810.2\u4e2d\uff0cOracle\u5bf9\u4e8e\u5206\u533a\u5217\u7684\u9501\u5b9a\u7684\u652f\u6301\u662f\u5b58\u5728\u95ee\u9898\u7684\u3002\u67e5\u8be2\u4e86\u4e00\u4e0bMOS\uff0cOracle\u5c06\u8fd9\u4e2a\u95ee\u9898\u786e\u8ba4\u4e3a\u5185\u90e8BUG\uff1a7240460\uff0c\u8fd9\u4e2a\u95ee\u9898\u572811.1.0.7\u4e2d\u88abFIXED\u3002
\n\u800c\u572811.2\u4e2d\uff0c\u8fd9\u4e2a\u95ee\u9898\u4ee5\u53ca\u4e0d\u5b58\u5728\u4e86\uff1a<\/p>\n
SQL> select * from v$version;\r\nBANNER\r\n----------------------------------------------------------------------------\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nPL\/SQL Release 11.2.0.3.0 - Production\r\nCORE 11.2.0.3.0 Production\r\nTNS for Solaris: Version 11.2.0.3.0 - Production\r\nNLSRTL Version 11.2.0.3.0 - Production\r\nSQL> select owner, table_name, partition_name, stattype_locked\r\n 2 from dba_tab_statistics\r\n 3 where owner = 'TEST'\r\n 4 and table_name = 'T_PART';\r\nOWNER TABLE_NAME PARTITION_NAME STATT\r\n---------- ------------ --------------- -----\r\nTEST T_PART\r\nTEST T_PART P2\r\nTEST T_PART P3\r\nTEST T_PART P4\r\nTEST T_PART P5\r\nTEST T_PART PMAX\r\n6 rows selected.\r\nSQL> exec dbms_stats.lock_partition_stats('TEST', 'T_PART', 'P2')\r\nPL\/SQL procedure successfully completed.\r\nSQL> select owner, table_name, partition_name, stattype_locked\r\n 2 from dba_tab_statistics\r\n 3 where owner = 'TEST'\r\n 4 and table_name = 'T_PART';\r\nOWNER TABLE_NAME PARTITION_NAME STATT\r\n---------- ------------ --------------- -----\r\nTEST T_PART\r\nTEST T_PART P2 ALL\r\nTEST T_PART P3\r\nTEST T_PART P4\r\nTEST T_PART P5\r\nTEST T_PART PMAX\r\n6 rows selected.<\/pre>\n","protected":false},"excerpt":{"rendered":"Oracle10g\u7684DBA_TAB_STATISTICS\u89c6\u56fe\u7684STATTYPE_LOCKED\u5217\u6ca1\u6709\u6b63\u786e\u7684\u663e\u793a\u7ed3\u679c\u3002 \u770b\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\uff1a 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 … Continue reading