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

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

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)

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
-----

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

--转自