Oracle19c impdp失败报ORA-20000案例

在Oracle 19.28.0.0.0中使用impdp导入数据时,又一次遇到了ORA-20000错误.具体如下所示:

1. .................................................
2. Processing object type SCHEMA_EXPORT/TABLE/COMMENT
3. Processing object type SCHEMA_EXPORT/VIEW/VIEW
4. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
5. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
6. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
7. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
8. Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
9. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
10. XXXXX.PK_XXXX_XXXX : sqlerrm = ORA-20000: Unable to set values for index PK_XXXX_XXXX: does not exist or insufficient privileges
11. Importing statistics failed for 1 object(s);
12. .................................................

复制代码

上一次遇到这个错误,已经在这篇文章中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, 两者非常相似. 具体介绍如下所示:

1. Applies To
2. All Users
3. Summary
5. ORA-20000 is reported when DataPump import loads SCHEMA_EXPORT/STATISTICS/MARKER:
7. > expdp "/ AS SYSDBA" directory=<DIR> schemas=<SCHEMA_NAME> dumpfile=<dmp_file> logfile=<log_file> ENCRYPTION_PASSWORD=********
9. > impdp "/ AS SYSDBA" directory=<DIR> TABLE_EXISTS_ACTION=REPLACE dumpfile=<dmp_file> logfile=<log_file> ENCRYPTION_PASSWORD=********
11. Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
12. <SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
13. <SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
14. <SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
15. <SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
16. <SCHEMA_NAME>.SYS_CXXXXXXX : sqlerrm = ORA-20000: Unable to set values for index SYS_CXXXXXXX: does not exist or insufficient privileges
17. ...
20. Solution
22. If you are using 19c, try applying patch 30978304 first.
23. Patch 30978304 is not available in versions prior to 18c.
27. Use the workaround:
29. - run impdp with EXCLUDE=STATISTICS option.
33. Attachments :
34. Cause
37. The cause  was investigated in the unpublished Bug 26380126 - IMPDP GETTING ORA-20000 ON STATISTICS/MARKER IMPORT STAGE.
39. 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.
43. Also, there are similar reports in BUG 30978304.
45. <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
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！