Oracle19c impdp失败报ORA-20000案例

在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

解决方案

1. impdp命令加上参数EXCLUDE=STATISTICS即可避免遇到这个错误.
2. 安装补丁30978304也可以解决这个问题.

另外,需要注意的是这个错误不能忽略, 它会导致主键对应的唯一索引丢失.如果在使用数据泵导入过程中遇到这个问题,那么你应当谨慎对待这个错误. 最好按照上面解决方案中的一种方案去解决.

参考资料

(KB97324) ORA-20000 When Processing STATISTICS/MARKER By DataPump