爱华网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 ============(普通用户权限)==
SQL> exec dbms_stats.gather_table_stats(ownname =>'ECC_VIEW',tabname => 'TABLE_TEST1',cascade =>TRUE,estimate_percent => 20);
PL/SQL procedure successfully completed
////////////////
执行以下 脚本后正常
SQL> exec dbms_stats.unlock_schema_stats(ownname =>'test');
PL/SQL procedure successfully completed
////////////////////
发现一个用户下统计信息没有生成,
查询user_tab_modifications发现变动信息也超过10%
没有警告日志
执行
exec dbms_stats.gather_schema_stats(ownname =>'test',granularity => 'ALL',cascade => true);
还是没有生成
然后执行报
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
执行以下脚本后正常
SQL> execdbms_stats.unlock_schema_stats(ownname =>'test');===执行这个就正常了===
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).
