sqlerrm = ORA-20000: Unable to set values for index

导入遇到报错：

JYC_SPAS.SYS_C0054365 : sqlerrm = ORA-20000: Unable to set values for index SYS_C0054365: does not exist or insufficient privileges
Importing statistics failed for 1 object(s); 

从上述报错，明显是statistics 这块出问题了。其实可以忽略不管。直接再重新收集统计信息即可。

如果要导入不报错，那么再按如下处理思路处理即可。（一般从低版本导入高版本，我们强烈建议排除统计信息导入，然后在高版本库重新收集统计信息即可）

导库思路如下：
1.目标端删除用户数据
2.目标端排除统计信息重新导入
3.目标端重新收集统计信息

处理过程如下：

[oracle@jyc dmp]$ impdp \'/ as sysdba\' DIRECTORY=DMP FULL=Y PARALLEL=1 CLUSTER=N DUMPFILE=jyc-date2024010301.dmp logfile=imp-jyc.log

Import: Release 12.2.0.1.0 - Production on Thu Jan 4 09:31:51 2024

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" DIRECTORY=DMP FULL=Y PARALLEL=1 CLUSTER=N DUMPFILE=jyc-date2024010301.dmp logfile=imp-jyc.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JYC_SPAS"."MDM_MMASTER_AGREEMENT_PRICE"        0 KB       0 rows
. . imported "JYC_SPAS"."SYS_FILES_TEMPLATE"             8.492 KB       5 rows
. . imported "JYC_SPAS"."SYS_MENU"                       19.58 KB      53 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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
JYC_SPAS.SYS_C0054365 : sqlerrm = ORA-20000: Unable to set values for index SYS_C0054365: does not exist or insufficient privileges
Importing statistics failed for 1 object(s); 
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Thu Jan 4 09:32:38 2024 elapsed 0 00:00:47

[oracle@jyc dmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 4 09:37:19 2024

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop user JYC_SPAS cascade;

User dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@jyc dmp]$ impdp \'/ as sysdba\' DIRECTORY=DMP FULL=Y PARALLEL=1 CLUSTER=N DUMPFILE=jyc-date2024010301.dmp logfile=imp-jyc.log exclude=statistics

Import: Release 12.2.0.1.0 - Production on Thu Jan 4 09:38:02 2024

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" DIRECTORY=DMP FULL=Y PARALLEL=1 CLUSTER=N DUMPFILE=jyc-date2024010301.dmp logfile=imp-jyc.log exclude=statistics 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
。。。。。。
. . imported "JYC_SPAS"."SYS_FILES_TEMPLATE"             8.492 KB       5 rows
. . imported "JYC_SPAS"."SYS_MENU"                       19.58 KB      53 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Thu Jan 4 09:38:34 2024 elapsed 0 00:00:32

[oracle@jyc dmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 4 09:38:48 2024

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter cpu;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
cpu_count                            integer
24
parallel_threads_per_cpu             integer
2
resource_manager_cpu_allocation      integer
24
SQL> set time on
09:40:42 SQL> set timing on
09:40:42 SQL> alter session set workarea_size_policy=manual;

Session altered.

Elapsed: 00:00:00.00
09:40:42 SQL> alter session set sort_area_size=1073741820;

Session altered.

Elapsed: 00:00:00.00
09:40:42 SQL> alter session set sort_area_retained_size=1073741820;

Session altered.

Elapsed: 00:00:00.00
09:40:42 SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

Elapsed: 00:00:00.01
09:40:42 SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'JYC_SPAS',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>24);

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.98

相关参考：
ORA-20000 Unable to Set Values for Index XX: Does Not Exist or Insufficient Priv is Raised While Executing Impdp (Doc ID 2176364.1)

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database - Standard Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
SYMPTOMS
We will get following error while executing impdp.

In 11.2

ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "USER_B"."BITMAP_INDEX" does not exist or insufficient privileges	 
In 12.1

 	USER_B.BITMAP_INDEX : sqlerrm = ORA-20000: Unable to set values for index BITMAP_INDEX: does not exist or insufficient privileges
Importing statistics failed for 1 object(s);Job "USER_B"."SYS_IMPORT_FULL_01" completed with 2 error(s)
There are two users (USER_A / USER_B) and each user has objects as below and statistics are gotten for them.

USER_A:
1. btree_tbl(table) and btree_index(normal index)
2. bitmap_tbl(table) and bitmap_index(bitmap index)

USER_B:
1. btree_tbl(table) and btree_index(normal index)

Then execute expdp as USER_A:

expdp USER_A/USER_A directory=test_dir dumpfile=exp_data.dmp include=statistics tables=btree_tbl reuse_dumpfiles=y

And execute impdp as USER_B:

impdp USER_B/USER_B directory=test_dir dumpfile=exp_data.dmp remap_schema=user_a:user_b

Because expdp is using "tables=btree_tbl", statistics for only this table should be exported.

But the error is for bitmap_index of bitmap_tbl table which is not imported.

Testcase below reproduces the problem:

[Testcase]

conn / as sysdba

-- Create user and directory
create user USER_A identified by USER_A default tablespace users;
grant dba to USER_A;
grant unlimited tablespace to USER_A;
create user USER_B identified by USER_B default tablespace users;
grant dba to USER_B;
grant unlimited tablespace to USER_B;
create or replace directory TEST_DIR as '/tmp';
grant read, write on directory TEST_DIR to USER_A;
grant read, write on directory TEST_DIR to USER_B;

-- Create objects for USER_A
conn USER_A/USER_A
show user
create table btree_tbl (col1 number(1), col2 number(2));
create index btree_index on btree_tbl (col1);
create table bitmap_tbl (col1 number(1), col2 number(2));
create bitmap index bitmap_index on bitmap_tbl (col1);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'USER_A' ,tabname => 'BTREE_TBL');
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'USER_A' ,tabname => 'BITMAP_TBL');

-- Create objects for USER_B
conn USER_B/USER_B
show user
create table btree_tbl (col1 number(1), col2 number(2));
create index btree_index on btree_tbl (col1);

-- Execute expdp and impdp
!expdp USER_A/USER_A directory=test_dir dumpfile=exp_data.dmp include=statistics tables=btree_tbl reuse_dumpfiles=y
!impdp USER_B/USER_B directory=test_dir dumpfile=exp_data.dmp remap_schema=user_a:user_b 

 
 

 

CHANGES
 

CAUSE
 This problem is fixed by unpublished BUG 27761685.

SOLUTION
To avoid the issue use the workaround:

- Add include=index while executing expdp

  and

- Add include=statistics while executing impdp

expdp USER_A/USER_A directory=test_dir dumpfile=exp_data.dmp include=statistics,index tables=btree_tbl reuse_dumpfiles=y
impdp USER_B/USER_B directory=test_dir dumpfile=exp_data.dmp remap_schema=user_a:user_b include=statistics