[转帖]SAP之ORA-20005: object statistics are locked_Tomcat, WebLogic及J2EE讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  Tomcat, WebLogic及J2EE讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1583 | 回复: 0   主题: [转帖]SAP之ORA-20005: object statistics are locked        上一篇   下一篇 
gunrose
注册用户
等级:新兵
经验:36
发帖:79
精华:0
注册:2011-7-21
状态:离线
发送短消息息给gunrose 加好友    发送短消息息给gunrose 发消息
发表于: IP:您无权察看 2015-3-23 15:10:28 | [全部帖] [楼主帖] 楼主

在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).


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论