愛華網(wǎng)SQL> exec dbms_stats.unlock_table_stats(ownname =>'ECC_VIEW',tabname => 'TABLE_TEST1');
PL/SQL procedure successfully completed
or
SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname =>'ECC_VIEW');
PL/SQL procedure successfully completed ============(普通用戶權(quán)限)==
SQL> exec dbms_stats.gather_table_stats(ownname =>'ECC_VIEW',tabname => 'TABLE_TEST1',cascade =>TRUE,estimate_percent => 20);
PL/SQL procedure successfully completed
////////////////
執(zhí)行以下 腳本后正常
SQL> exec dbms_stats.unlock_schema_stats(ownname =>'test');
PL/SQL procedure successfully completed
////////////////////
發(fā)現(xiàn)一個用戶下統(tǒng)計信息沒有生成,
查詢user_tab_modifications發(fā)現(xiàn)變動信息也超過10%
沒有警告日志
執(zhí)行
exec dbms_stats.gather_schema_stats(ownname =>'test',granularity => 'ALL',cascade => true);
還是沒有生成
然后執(zhí)行報
begin dbms_stats.gather_table_stats(ownname => 'test',tabname=> 'TCCLICOMH',granularity => 'ALL',cascade => true);end;
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: 在 "SYS.DBMS_STATS", line 13056
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 2
執(zhí)行以下腳本后正常
SQL> execdbms_stats.unlock_schema_stats(ownname =>'test');===執(zhí)行這個就正常了===
PL/SQL procedure successfully completed
SQL>exec dbms_stats.gather_schema_stats(ownname =>'test',granularity => 'ALL',cascade => true);
PL/SQL procedure successfully completed
原因是因為使用impdp只導入metadata_only 或(expdp的時候使用了contend=metadata_only)時 沒有使用 useexclude=(table_statistics,index_statistics)
引起的
參考文檔
Symptoms
---------
Either ofthe following two error messages are signaled:
1.ORA-38029: object statistics are locked
2.ORA-20005: object statistics are locked (stattype = ALL)
Cause
---------
PossibleCause 1:
DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lockstatistics on the table.
PossibleCause 2:
Using import(imp) or data pump import (impdp) to import a table without dataresults in the table's statistics being locked in 10gR2.
PossibleCause 3:
After anIMPORT is finished for which ROWS=N, the statistics for all tablesimported will be locked.
Part NumberB14233-04 Database Readme 10g Release 2 (10.2) (39.5 OriginalExport/Import)
PossibleCause 4: If the table is a queue table then the statistics areintended to be empty and locked so that dynamic sampling will beused due to the table's volatility. During an upgrade to 10gR2statistics on queue tables are deleted and then locked. In 10gR2when a queue table is created statistics are locked while stillempty.
Solution
---------
If the tableis a queue table then the statistics should remain empty and lockedso that dynamic sampling is used due to the volatility of queuetables. If the table is not a queue table, unlock the statisticsusing DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statisticson the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS andthe force=>true parameter.
Toprevent import (imp) from locking the table's statistics whenimporting a table without therows (rows=n), use statistics=none. Toprevent data pump import (impdp) from locking the table'sstatistics when importing a table without the rows(content=metadata_only), useexclude=(table_statistics,index_statistics).
