oracle 由於impdp 引起的表統計資訊被鎖 ORA-20005: object statistics are locked

發現一個使用者下統計資訊沒有生成,
查詢user_tab_modifications 發現變動資訊也超過10%

沒有警告日誌
執行
exec dbms_stats.gather_schema_stats(ownname => 'test',granularity => 'ALL',cascade => true);
還是沒有生成

然後執行報
begin dbms_stats.gather_table_stats(ownname => 'test',tabname => 'TCCLICOMH',granularity => 'ALL',cascade => true); end;
 
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: 在 "SYS.DBMS_STATS", line 13056
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 2

執行以下指令碼後正常
SQL> exec dbms_stats.unlock_schema_stats(ownname => 'test');
 
PL/SQL procedure successfully completed
 
SQL>exec dbms_stats.gather_schema_stats(ownname => 'test',granularity => 'ALL',cascade => true);
 
PL/SQL procedure successfully completed


原因是因為使用impdp  只匯入metadata_only 或(expdp的時候使用了contend=metadata_only)時 沒有使用 use exclude=(table_statistics,index_statistics)
引起的



參考文件
 Symptoms
    ---------
    Either of the following two error messages are signaled:
    1. ORA-38029: object statistics are locked
    2. ORA-20005: object statistics are locked (stattype = ALL)

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

    Possible Cause 4: 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.

    Solution
    ---------
    If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables. If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter.

    To prevent import (imp) from locking the table's statistics when importing a table without therows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).