ORA-20005的解决方法

最新推荐文章于 2024-10-25 16:00:00 发布

转载最新推荐文章于 2024-10-25 16:00:00 发布 · 715 阅读

文章标签：

#数据库

本文探讨了在Oracle 10g中使用数据泵进行元数据导出和导入时，如何解决因默认启用自动统计信息收集而导致的统计信息锁定问题。提供了两种解决方案：解锁表统计信息或排除表统计信息的导入。

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3

This problem can occur on any platform.

DataPump Import without data (CONTENT=METADATA_ONLY) locks statistics.

Executing the DBMS_STATS.GATHER_TABLE_STATS to collect the statistics on the table imported, it fails with:

ORA-20005: object statistics are locked (stattype = ALL)

This is an expected behavior. in 10.2.0

The statistics are locked during a DataPump Import if export or import were performed with CONTENT = METADATA_ONLY.

This is because automatic statistics gathering is enabled by default in 10g. Therefore, the imported statistics, if not locked, are lost the next time the auto-stats job runs

To avoid the ORA-20005:

- Unlock the table statistics after the import:

execute DBMS_STATS.UNLOCK_TABLE_STATS('','

- Do not import the table statistics (EXCLUDE=TABLE_STATISTICS impdp parameter)

运行下列SQL

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

OWNER TABLE_NAME STATT

------------------------------

APPLSYS WF_NOTIFICATION_OUT ALL

1 row selected.

SQL> exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');

PL/SQL procedure successfully completed.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

no rows selected

问题解决。

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