« 恩墨科技的第二次培训明日（11.5）开始 | Blog首页 | 恩墨科技的第二次培训(2009年11月)圆满结束 »
SAP之ORA-20005: object statistics are locked
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2009/11/sap_stat_locked.html
在SAP的数据库优化中，当尝试收集一个数据表的数据时，遇到如下错误：链接：https://www.eygle.com/archives/2009/11/sap_stat_locked.html
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
供参考！
-The End-
历史上的今天...
>> 2011-11-05文章:
>> 2007-11-05文章:
>> 2005-11-05文章:
By eygle on 2009-11-05 08:38 | Comments (0) | FAQ | Oracle12c/11g | 2440 |