ORA-39001: invalid argument value ORA-01775: looping chain of synonyms

Error:

When running DataPump import/export (impdp/expdp) the following errors occur:

Export: Release 11.2.0.3.0 - Production on Sun Jan 28 10:51:40 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms

Reason:

Apparently, the SYS_IMPORT_SCHEMA_01 table is a master table being created during schema impdp. If the impdp is carried out normally then this table would be dropped automatically. In case there is an abnormal termination of the impdp then, the table might still remain.

running this query shows that a public synonym (not created by DataPump) still exists with the name "SYS_IMPORT_SCHEMA_01".

Dropping the synonym SYS_IMPORT_SCHEMA_01 should resolve this issue.

Solution

SQL> select owner, object_name, object_type, status from dba_objects where object_name like '%SYS_IMPORT_SCHEMA_01%';
OWNER           OBJECT_NAME            OBJECT_TYPE      STATUS
--------------- ---------------------- ---------------- -------
PUBLIC          SYS_IMPORT_SCHEMA_01   SYNONYM           VALID
connect / as sysdba
drop public synonym sys_import_schema_01;