在Oracle 19.28.0.0.0中使用impdp導入數據時,又一次遇到了ORA-20000錯誤.具體如下所示:
.................................................
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
XXXXX.PK_XXXX_XXXX : sqlerrm = ORA-20000: Unable to set values for index PK_XXXX_XXXX: does not exist or insufficient privileges
Importing statistics failed for 1 object(s);
.................................................
上一次遇到這個錯誤,已經在這篇文章中ORA-20000: Unable to set values for index xxx: does not exist or insufficient privileges中總結了.這一次遇到的錯誤,搜索官方文檔時發現這篇文章(KB97324) ORA-20000 When Processing STATISTICS/MARKER By DataPump中介紹unpublished Bug 26380126 也會觸發這個報錯,相比之前的BUG 30978304, 兩者非常相似. 具體介紹如下所示:
Applies To
All Users
Summary
ORA-20000 is reported when DataPump import loads SCHEMA_EXPORT/STATISTICS/MARKER:
> expdp \"/ AS SYSDBA\" directory=<DIR> schemas=<SCHEMA_NAME> dumpfile=<dmp_file> logfile=<log_file> ENCRYPTION_PASSWORD=********
> impdp \"/ AS SYSDBA\" directory=<DIR> TABLE_EXISTS_ACTION=REPLACE dumpfile=<dmp_file> logfile=<log_file> ENCRYPTION_PASSWORD=********
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
<SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
<SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
<SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
<SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
<SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
...
Solution
If you are using 19c, try applying patch 30978304 first.
Patch 30978304 is not available in versions prior to 18c.
Use the workaround:
- run impdp with EXCLUDE=STATISTICS option.
Attachments :
Cause
The cause was investigated in the unpublished Bug 26380126 - IMPDP GETTING ORA-20000 ON STATISTICS/MARKER IMPORT STAGE.
Bug 26380126 was closed as duplicate of the Enhancement Request 33375178 - ORA-20000 WHEN ATTEMPTING IMPORT OF STATS FOR UNNEEDED SYSTEM-CREATED INDEX, still in progress.
Also, there are similar reports in BUG 30978304.
<BUG 30978304> - ORA-20000 DURING IMPDP WITH STATS AND THE UNIQUE INDEX FOR THE PK IS NOT CREATED
解決方案
- impdp命令加上參數EXCLUDE=STATISTICS即可避免遇到這個錯誤.
- 安裝補丁30978304也可以解決這個問題.
另外,需要注意的是這個錯誤不能忽略, 它會導致主鍵對應的唯一索引丟失.如果在使用數據泵導入過程中遇到這個問題,那麼你應當謹慎對待這個錯誤. 最好按照上面解決方案中的一種方案去解決.
參考資料
(KB97324) ORA-20000 When Processing STATISTICS/MARKER By DataPump
