ORA-39001: invalid argument value ORA-01775: looping chain of synonyms during export import expdp impdp

ORA- Error :



$expdp parfile=exp_mis_rpt_base.par
Export: Release 11.2.0.4.0 - Production on Tue Jul 14 09:30:48 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms



Cause :

During export or import existing SYS_EXPORT_SCHEMA_n tables will be reused , however invalid public synonym references to these tables leads to ORA-01775: looping chain of synonyms


Solution:


Drop the invalid public synonyms referencing non-existing SYS_EXPORT_SCHEMA_n tables.

SQL> col OBJECT_NAME for a40
SQL> select object_name, owner, object_type, status from dba_objects where object_name like 'SYS_EXPORT_SCHEMA%' and object_type='SYNONYM' and STATUS ='INVALID';


OBJECT_NAME OWNER OBJECT_TYPE STATUS
---------- ------ -------------- -------
SYS_EXPORT_SCHEMA_12 PUBLIC SYNONYM INVALID
SYS_EXPORT_SCHEMA_05 PUBLIC SYNONYM INVALID
SYS_EXPORT_SCHEMA_03 PUBLIC SYNONYM INVALID
SYS_EXPORT_SCHEMA_10 PUBLIC SYNONYM INVALID
SYS_EXPORT_SCHEMA_08 PUBLIC SYNONYM INVALID
SYS_EXPORT_SCHEMA_07 PUBLIC SYNONYM INVALID

6 rows selected.


SQL> select ' drop public synonym ' || object_name || ' ;' from dba_objects where object_name like 'SYS_EXPORT_SCHEMA%' and object_type='SYNONYM' and STATUS ='INVALID';

'DROPPUBLICSYNONYM'||OBJECT_NAME||';'
--------------------------------------------
drop public synonym SYS_EXPORT_SCHEMA_12 ;
drop public synonym SYS_EXPORT_SCHEMA_05 ;
drop public synonym SYS_EXPORT_SCHEMA_03 ;
drop public synonym SYS_EXPORT_SCHEMA_10 ;
drop public synonym SYS_EXPORT_SCHEMA_08 ;
drop public synonym SYS_EXPORT_SCHEMA_07 ;


6 rows selected.

SQL>drop public synonym SYS_EXPORT_SCHEMA_12 ;Synonym dropped.

SQL>drop public synonym SYS_EXPORT_SCHEMA_05 ;Synonym dropped.

SQL>drop public synonym SYS_EXPORT_SCHEMA_03 ;Synonym dropped.

SQL>drop public synonym SYS_EXPORT_SCHEMA_10 ;Synonym dropped.

SQL>drop public synonym SYS_EXPORT_SCHEMA_08 ;Synonym dropped.

SQL>drop public synonym SYS_EXPORT_SCHEMA_07 ;Synonym dropped.


Re-run the export/import.



Export: Release 11.2.0.4.0 - Production on Tue Jul 14 09:34:58 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Starting "RPT_BASE"."SYS_EXPORT_SCHEMA_01": rpt_base/********@mis parfile=exp_mis_rpt_base.par

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 875.6 MB
. . exported "RPT_BASE"."SP_ENTITY_VISIBILITY" 53.60 MB 2634740 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
. . exported "RPT_BASE"."XP_CUST_SEGME_MAP" 43.42 MB 561120 rows
. . exported "RPT_BASE"."XP_ALLO_FACILITY_KEY" 163.8 MB 5234224 rows
. . exported "RPT_BASE"."XP_SYST_DATA" 25.87 MB 437499 rows
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK

.

.

.

Success !