【ORA】ORA-38029 & ORA-20005

最新推荐文章于 2024-07-04 18:22:35 发布

cme11403

最新推荐文章于 2024-07-04 18:22:35 发布

阅读量268

点赞数

文章标签：数据库

从10g导出了一致表到本地的11g环境中做测试。导入后习惯的去收集一下统计信息。遇到了ORA-20005和ORA-38029错误。

/home/oracle$oerr ora 38029
38029, 00000, "object statistics are locked"
// *Cause: An attept was made to modify optimizer statistics of the object.
// *Action: Unlock statistics with the DBMS_STATS.UNLOCK_TABLE_STATS procedure
// on base table(s). Retry the operation if it is okay to update statistics.

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

Oracle Database Readme
10g Release 2 (10.2)
Part Number B14233-04
39.5 Original Export/Import
After an IMPORT is finished for which ROWS=N, the statistics for all tables imported will be locked.

Solution
- Unlock the table statistics after the import:
execute DBMS_STATS.UNLOCK_TABLE_STATS('','

我遇到的可能是ROWS=N这种情况，在开始导入到时候确实是加了ROWS=N这个参数。

示例：
SQL> EXEC DBMS_STATS.gather_table_stats('REPORT','SWFLOG');
begin DBMS_STATS.gather_table_stats('REPORT','SWFLOG'); end;

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: 在 "SYS.DBMS_STATS", line 20337
ORA-06512: 在 "SYS.DBMS_STATS", line 20360
ORA-06512: 在 line 1

SQL> ANALYZE TABLE SWFLOG COMPUTE STATISTICS;

ANALYZE TABLE SWFLOG COMPUTE STATISTICS
ORA-38029: 对象统计信息已锁定

解决方法：

SQL> execute DBMS_STATS.UNLOCK_TABLE_STATS('REPORT','SWFLOG');
PL/SQL procedure successfully completed

SQL> EXEC DBMS_STATS.gather_table_stats('REPORT','SWFLOG');
PL/SQL procedure successfully completed

SQL> ANALYZE TABLE SWFLOG COMPUTE STATISTICS;
Table analyzed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26892340/viewspace-722175/，如需转载，请注明出处，否则将追究法律责任。