ORA-39083: Object type INDEX_STATISTICS failed to create with error:-ORA-20000: INDEX “SYS”.”IDX_TABX” does not exist or insufficient privileges

Cause:

During import of data using impdp over network link, there are some index statistics error due to privilege issue

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES

Import: Release 12.1.0.2.0 - Production on Mon Aug 16 16:25:20 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES
Startup took 4 seconds
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 3 seconds
Total estimation using BLOCKS method: 95.75 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 11 seconds
. . imported "TEST"."TABX"                              1048576 rows in 1276 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "SYS"."IDX_ID" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "SYS"."IDX_TABX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NUL
     Completed 2 INDEX_STATISTICS objects in 3 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 4 seconds
     Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 1276 seconds
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at Mon Aug 16 16:47:02 2021 elapsed 0 00:21:41


Workaround:

Use exclude=statistics parameter to exclude the gather of statistics

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES exclude=statistics

Import: Release 12.1.0.2.0 - Production on Mon Aug 16 16:55:10 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES exclude=statistics
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 0 seconds
Total estimation using BLOCKS method: 95.75 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 2 seconds
. . imported "TEST"."TABX"                              1048576 rows in 20 seconds
     Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 20 seconds
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Aug 16 16:55:37 2021 elapsed 0 00:00:25

Gather statistics for the index separately. In my case the index was not created itself. You have to get metadata of index and create it manually using dbms_metadata.get_ddl package

Example : select DBMS_METADATA.GET_DDL(‘INDEX‘, index_name) from all_indexes

SQL> exec dbms_stats.gather_index_stats('TEST','IDX_TABX');
BEGIN dbms_stats.gather_index_stats('TEST','IDX_TABX'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX "TEST"."IDX_TABX", insufficient privileges
or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 26193
ORA-06512: at line 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s