ORA-20005: object statistics are locked
連結：
在SAP的資料庫最佳化中，當嘗試收集一個資料表的資料時，遇到如下錯誤：
這個提示告訴我們，這個表的統計計息被鎖定，不允許更新，這是Oracle 10g的一個新特性，允許我們鎖定某些物件的統計資訊：
當然可以找到相應的解鎖過程：
這些鎖定資訊可以透過DBA的字典表檢視：
確認一下SAP鎖定了哪些資訊：
可以透過簡單的測試瞭解整個功能：
而在Oracle10g中，這個鎖定可能和imp/impdp時制定rows=n的選項有關：
以下是幾個網友遇到問題的參考連結：
http://space.itpub.net/9252210/viewspace-607376
http://space.itpub.net/9252210/viewspace-607297
http://yangtingkun.itpub.net/post/468/489433
供參考！
-The End-
在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-
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-627007/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- IMP ORA-20005: object statistics are locked(一)Object
- IMP ORA-20005: object statistics are locked(二)Object
- ORA-20005：object statistics are locked (stattype = ALL)Object
- ORA-20005: object statistics are locked (stattype = ALL)Object
- ORA-38029: object statistics are lockedObject
- oracle 由於impdp 引起的表統計資訊被鎖 ORA-20005: object statistics are lockedOracleObject
- oracle 由於impdp 引起的表統計資訊被鎖 ORA-20005: object statistics are lockedOracleObject
- v$lockv和$locked_object的區別Object
- 學習動態效能表(八)-(3)-V$LOCKED_OBJECTObject
- oracle鎖表問題處理 v$lock v$locked_objectOracleObject
- Oracle動態效能檢視學習之v$lock & v$locked_objectOracleObject
- (轉)學習Oracle動態效能表-(3)V$LOCK,V$LOCKED_OBJECTOracleObject
- 關聯v$session,v$locked_object,dba_objects查出鎖死會話及物件SessionObject會話物件
- 10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列對分割槽鎖定顯示為空
- A. Locked Calculator
- Lombok @Locked指南Lombok
- Database StatisticsDatabase
- DELETE STATISTICSdelete
- 10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列對分割槽鎖定顯示為空的解決
- Statistics related viewView
- Time Model Statistics
- oracle之StatisticsOracle
- Import parameter: STATISTICSImport
- SELECT ... FOR UPDATE SKIP LOCKED;
- httpd dead but subsys lockedhttpd
- Saving Original Statistics and Gathering New Statistics
- SciTech-Mathematics-Probability+Statistics-7 Key Statistics Concepts
- ORA-20005的解決方法
- SciTech-Mathmatics-Probability+Statistics-VI-Statistics:Quantifing Uncertainty + Regression Analysis）AI
- How restore CBO statisticsREST
- Oracle Column Group StatisticsOracle
- Explain for the Statistics of Execution PlanAI
- [PT]Column Histogram StatisticsHistogram
- Statistics and Data Analysis for BioinformaticsORM
- 解決ora-20005的遊標
- sqlite3.OperationalError: database is lockedSQLiteErrorDatabase
- SciTech-Statistics-英語授課：Business Statistics商務統計
- 計算機中了locked勒索病毒怎麼辦，locked勒索病毒解密，資料恢復計算機解密資料恢復