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
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;
