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('','

');

OR

- 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/，如需轉載，請註明出處，否則將追究法律責任。