Solution to Looping Chain of Synonyms Problem During Export(EXPDP)

Solution to Looping Chain of Synonyms Problem During Export(EXPDP)

While I was trying to export using data pump i found the following error. Later I found a solution that worked.

The expdp command was:


bash-3.00$ expdp user/pass schemas=schema_name directory=db_back dumpfile=test_27022012.dmp VERSION=10.2

Export: Release 11.2.0.1.0 - Production on Tue Feb 28 10:27:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms

The Solution:

Issue the following query to find the synonyms with status valid.
Select owner, object_name, object_type, status
  from dba_objects
 where object_name like '%SYS_EXPORT%';

Drop all the synonyms that have Status Valid:

DROP PUBLIC SYNONYM SYS_EXPORT_SCHEMA_01 ;

Run the expdp command again.



-------------------------------------------------------------------------------------
Happy to Help !!!