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