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

Last updated on SEPTEMBER 22, 2016

Applies to:

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]
Information in this document applies to any platform.

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"."BITMAP1_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 bellow 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.

 

[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 

 

 

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms