Resolution case with ORA-39154 errors when impdp is executed

A data table Import and Export Operation encountered a ORA-39154 when using impdp import, it took some time to solve. Below I actually restored this error in the test environment, and

A data table Import and Export Operation encountered a ORA-39154 when using impdp import, it took some time to solve. Below I actually restored this error in the test environment, and

A data table Import and Export Operation encountered a ORA-39154 when using impdp import, it took some time to solve.
Below I actually restored this error in the test environment, and attached the solution and solution.

##### Create a test table, but the index of the table is created in another schema
Sqlplus ad/123456
Create table adtab1 tablespace ts_pub as select * from all_users;
SQL> select count (*) from adtab1;

COUNT (*)
----------
45

Sqlplus mng/789012
Create index ind_adtab1_uid on ad. adtab1 (user_id );
Create index ind_adtab1_crt on ad. adtab1 (created );

##### Create a Directory object on the source database connected to expdp and the target database connected to impdp, and grant the read and write permissions of the execution user ad on the directory.
Sqlplus '/as sysdba'
Create or replace directory tmpdir as '/home/Oracle/chh /';
Grant read, write on directory tmpdir to ad;

##### Export a table from the source database as sysdba
Expdp \ "/as sysdba \" tables = ad. adtab1 directory = tmpdir dumpfile = ad. adtab1.dmp logfile = exp_ad.adtab1.log reuse_dumpfiles = yes

--- The export process is normal.
Starting "SYS ". "SYS_EXPORT_TABLE_01": "/******** as sysdba" tables = ad. adtab1 directory = tmpdir dumpfile = ad. adtab1.dmp logfile = exp_ad.adtab1.log reuse_dumpfiles = yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
.. Exported "AD". "ADTAB1" 6.781 KB 45 rows
Master table "SYS". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for SYS. SYS_EXPORT_TABLE_01 is:
/Home/oracle/chh/ad. adtab1.dmp
Job "SYS". "SYS_EXPORT_TABLE_01" successfully completed at 08:17:27

##### Transfer the dmp file to the target database and run impdp as the ad user
REVOKE IMP_FULL_DATABASE from ad;
Impdp ad/123456 directory = tmpdir dumpfile = ad. adtab1.dmp logfile = imp_ad.adtab1.log

--- A ORA-39154 error occurs during the import process, prompting that the imported content contains an object that does not belong to the AD user, this part of the object is not properly imported, but the ad. adtab1 table has been imported successfully
The ORA-39154: Objects from foreign schemas have been removed from import
Master table "AD". "SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD". "SYS_IMPORT_FULL_01": ad/********* directory = tmpdir dumpfile = ad. adtab1.dmp logfile = imp_ad.adtab1.log
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.. Imported "AD". "ADTAB1" 6.781 KB 45 rows
Job "AD". "SYS_IMPORT_FULL_01" successfully completed at 08:20:11

Error Cause Analysis:
Because the imported content contains statistics, the operations related to statistics are performed in sys. impdp_stats table (can be seen from the impdp generated SQL script), ad users need to grant imp_full_database permission to import this part of the statistical information, this should be the cause of ORA-39154

--- Index not imported
SQL> select count (*) from adtab1;

COUNT (*)
----------
45

SQL> select index_name, table_name from dba_indexes where table_name = 'adtab1 ';

No rows selected

##### Assigned to the ad user imp_full_database permissions after the impdp again, this time the ORA-39083 replaces the ORA-39154, the problem lies in the MNG. IND_ADTAB1_UID, MNG. when the IND_ADTAB1_CRT indexes generate statistics, the two indexes do not exist. So far, we have discovered that the index and table are not in the same schema: The table is under the ad user, indexing is built on the mng user, which may be a mistake of developers. We will not discuss whether the indexing is reasonable for the moment.
Grant imp_full_database to ad;

Impdp ad/123456 directory = tmpdir dumpfile = ad. adtab1.dmp logfile = imp_ad.adtab1.log
Master table "AD". "SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "AD". "SYS_IMPORT_FULL_01": ad/********* directory = tmpdir dumpfile = ad. adtab1.dmp logfile = imp_ad.adtab1.log
Processing object type TABLE_EXPORT/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.. Imported "AD". "ADTAB1" 6.781 KB 45 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG". "IND_ADTAB1_UID" 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, California, n10, n11, n12, d1, cl1) VALUES (''I ', 6,: 1,: 2,: 3,: 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13, NULL,: 14 ,:
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "MNG". "IND_ADTAB1_CRT" 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, California, n10, n11, n12, d1, cl1) VALUES (''I ', 6,: 1,: 2,: 3,: 4,: 5,: 6,: 7,: 8,: 9,: 10,: 11,: 12,: 13, NULL,: 14 ,:
Job "AD". "SYS_IMPORT_FULL_01" completed with 2 error (s) at 08:43:01

--- Check the target database. Only tables are imported.
SQL> select count (*) from adtab1;

COUNT (*)
----------
45

SQL> select index_name, table_name from dba_indexes where table_name = 'adtab1 ';

No rows selected