ORA-20005: object statistics are locked (stattype = ALL)
解決方案：
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:
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('<user name>','<table name>');
OR
- Do not import the table statistics (EXCLUDE=TABLE_STATISTICS impdp parameter)
執行下列的語法來解決問題
指定Table：
exec DBMS_STATS.UNLOCK_TABLE_STATS('OWNER', 'TABLE_NAME');
指定Schmea：
exec dbms_stats.unlock_schema_stats('SCHEMA_NAME');
檢查是否還有其他的Statistics Lock的Table：
SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;
no rows selected
重新執行Gather Schema Statistics就成功了。
