eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 恩墨科技的第二次培训明日(11.5)开始 | Blog首页 | 恩墨科技的第二次培训(2009年11月)圆满结束 »

SAP之ORA-20005: object statistics are locked

在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

供参考!

-The End-



历史上的今天...
    >> 2011-11-05文章:
    >> 2007-11-05文章:
    >> 2005-11-05文章:
           笔记-SUN 4500硬盘损坏

无觅

By eygle on 2009-11-05 08:38 | Comments (0) | FAQ | Oracle12c/11g | 2440 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com