My Oracle Support Banner

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 APRIL 02, 2024

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]
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

To view full details, sign in with your My Oracle Support account.

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

In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.