问题描述
在SAP的数据库优化中，当尝试收集一个数据表的数据时，遇到如下错误：
SQL> exec dbms_stats.gather_table_stats('SAPSR','QIN');
BEGIN dbms_stats.gather_table_stats('SAPSR3','TRFCQIN'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1专家解答
这个提示告诉我们，这个表的统计计息被锁定，不允许更新，这是Oracle 10g的一个新特性，允许我们锁定某些对象的统计信息：
PROCEDURE LOCK_TABLE_STATS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN STATTYPE VARCHAR2 IN DEFAULT PROCEDURE LOCK_PARTITION_STATS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN PARTNAME VARCHAR2 IN PROCEDURE LOCK_SCHEMA_STATS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN STATTYPE VARCHAR2 IN DEFAULT
当然可以找到相应的解锁过程：
PROCEDURE UNLOCK_PARTITION_STATS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN PARTNAME VARCHAR2 IN PROCEDURE UNLOCK_SCHEMA_STATS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN STATTYPE VARCHAR2 IN DEFAULT PROCEDURE UNLOCK_TABLE_STATS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN STATTYPE VARCHAR2 IN DEFAULT
这些锁定信息可以通过DBA的字典表查看：
SQL> desc dba_tab_statistics Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30) PARTITION_POSITION NUMBER SUBPARTITION_NAME VARCHAR2(30) SUBPARTITION_POSITION NUMBER OBJECT_TYPE VARCHAR2(12) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER AVG_CACHED_BLOCKS NUMBER AVG_CACHE_HIT_RATIO NUMBER SAMPLE_SIZE NUMBER LAST_ANALYZED DATE GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) STATTYPE_LOCKED VARCHAR2(5) STALE_STATS VARCHAR2(3)确认一下SAP锁定了哪些信息：
SQL> select owner,table_name,num_rows,blocks,avg_space,last_analyzed,stattype_locked 2 from dba_tab_statistics where STATTYPE_LOCKED is not null and rownum <200; OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE LAST_ANALYZED STATT --------------- ------------------------------ ---------- ---------- ---------- --------------- ----- SYS AQ_EVENT_TABLE ALL SYS AQ_SRVNTFN_TABLE ALL SYSTEM DEF$_AQCALL ALL SYSTEM DEF$_AQERROR ALL SYS SCHEDULER$_JOBQTAB ALL SYS SCHEDULER$_EVENT_QTAB ALL SYS KUPC$DATAPUMP_QUETAB ALL SYS AQ$_MEM_MC ALL SYS ALERT_QT ALL SYS SYS$SERVICE_METRICS_TAB ALL SAPSR3 DDXTF 3579 151 0 26-JUN-08 ALL SAPSR3 DDXTT 467 244 0 26-JUN-08 ALL SAPSR3 ARFCRSTATE 3390 200 0 26-JUN-08 ALL SAPSR3 ARFCSDATA 419227 120000 0 26-JUN-08 ALL SAPSR3 ARFCSSTATE 331849 30000 0 26-JUN-08 ALL SAPSR3 QREFTID 330878 4000 0 26-JUN-08 ALL SAPSR3 TRBAT 80 20 0 26-JUN-08 ALL SAPSR3 SXMSCLUP 1296545 114389 0 26-JUN-08 ALL SAPSR3 SXMSCLUP2 1296545 114389 0 26-JUN-08 ALL SAPSR3 SXMSCLUR 1296948 180456 0 26-JUN-08 ALL SAPSR3 SXMSCLUR2 1296948 180456 0 26-JUN-08 ALL SAPSR3 SXMSPERRO2 1600 28 0 26-JUN-08 ALL SAPSR3 SXMSPERROR 1600 28 0 26-JUN-08 ALL SAPSR3 SXMSPVERS 1296545 17745 0 26-JUN-08 ALL SAPSR3 SXMSPVERS2 1296545 17745 0 26-JUN-08 ALL SAPSR3 TATAF 2952 103 0 26-JUN-08 ALL SAPSR3 TBTCO 5078 244 0 22-JUN-08 ALL SAPSR3 TRFCQDATA 71165 30000 0 26-JUN-08 ALL SAPSR3 TRFCQIN 20994 1000 0 26-JUN-08 ALL SAPSR3 TRFCQOUT 331796 13000 0 26-JUN-08 ALL SAPSR3 TRFCQSTATE 29575 2000 0 26-JUN-08 ALL SAPSR3 TRBAT2 79 43 0 26-JUN-08 ALL SAPSR3 SXMSPEMAS 435530 11369 0 26-JUN-08 ALL SAPSR3 SXMSPEMAS2 435530 11369 0 26-JUN-08 ALL SAPSR3 SXMSPMAST 435530 20041 0 26-JUN-08 ALL SAPSR3 SXMSPMAST2 435530 20041 0 26-JUN-08 ALL 36 rows selected.
可以通过简单的测试了解整个功能：
SQL> select stattype_locked
2 from USER_TAB_STATISTICS where table_name='EYGLE';
STATT
-----
SQL> exec dbms_stats.lock_table_stats('EYGLE','EYGLE');
PL/SQL procedure successfully completed.
SQL> select stattype_locked from USER_TAB_STATISTICS where table_name='EYGLE';
STATT
-----
ALL
SQL> exec dbms_stats.gather_table_stats('EYGLE','EYGLE');
BEGIN dbms_stats.gather_table_stats(user,'a'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1
SQL> exec dbms_stats.unlock_table_stats('EYGLE','EYGLE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('EYGLE','EYGLE');
PL/SQL procedure successfully completed.
SQL> select stattype_locked from USER_TAB_STATISTICS where table_name='EYGLE';
STATT
-----而在Oracle10g中，这个锁定可能和imp/impdp时制定rows=n的选项有关：
Symptoms --------- Either of the following two error messages are signaled: 1. ORA-38029: object statistics are locked 2. ORA-20005: object statistics are locked (stattype = ALL) Cause --------- Possible Cause 1: DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table. Possible Cause 2: Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2. Possible Cause 3: After an IMPORT is finished for which ROWS=N, the statistics for all tables imported will be locked. Part Number B14233-04 Database Readme 10g Release 2 (10.2) (39.5 Original Export/Import) Possible Cause 4: If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table's volatility. During an upgrade to 10gR2 statistics on queue tables are deleted and then locked. In 10gR2 when a queue table is created statistics are locked while still empty. Solution --------- If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables. If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter. To prevent import (imp) from locking the table's statistics when importing a table without therows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).
以下是几个网友遇到问题的参考链接：
http://space.itpub.net/9252210/viewspace-607376
http://space.itpub.net/9252210/viewspace-607297
http://yangtingkun.itpub.net/post/468/489433
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
