執行Gather Schema Statistics錯誤:ORA-20005: object statistics are locked

新的Oracle 11g資料是由datapump移轉過來的,在執行Gather Schema Statistics時發生錯誤:
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就成功了。

Oracle Data Gurad Archive Gap