I faced an error with datapump while taking export backup. I came across ORA-01775 which is looping chain of synonyms. No idea what it was. The command goes this way,

expdp dumpfile=EXPORT_BACKUP_DIR:expdp_tables_ORCL.dmp logfile=EXPORT_BACKUP_DIR:expdp_tables_ORCL.log tables=CLICKHR_INTERNAL_TCPS.PAGE, CLICKHR_INTERNAL_TCPS.TEMPLATE

0283(oracle)[ORCL]:/home/oracle> s=CLICKHR_INTERNAL_TCPS.PAGE, CLICKHR_INTERNAL_TCPS.TEMPLATE                                                   <

Export: Release 11.2.0.2.0 - Production on Mon Nov 12 13:43:50 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.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

0283(oracle)[ORCL]:/home/oracle> exit

hmmmm what…..

To diagnose this error, we can run 1775 trace.

SQL> connect / as sysdba
SQL> alter system set events '1775 trace name ERRORSTACK level 3';

SQL> alter system set events '1775 trace name errorstack off';

The trace file will output the following:

ksedmp: internal or fatal error
ORA-01775: looping chain of synonyms
Current SQL statement for this session:
SELECT COUNT(*) FROM SYS_EXPORT_TABLE_01

Basically SYS_EXPORT_SCHEMA_01 table is a master table being created during schema export. In my case its SYS_EXPORT_TABLE_01. If the expdp is carried out normally then this table would be dropped automatically. In case there is an abnormal termination of the expdp then, the table might still remain.

Run the below query. This will show that a public synonym (not created by DataPump) still exists with the name “SYS_EXPORT_TABLE_01”.

SQL> col OBJECT_NAME for a30
SQL> select owner, object_name, object_type, status from dba_objects where object_name like '%SYS_EXPORT%';

OWNER     OBJECT_NAME            OBJECT_TYPE         STATUS
--------- ---------------------- ------------------- --------
PUBLIC    SYS_EXPORT_TABLE_01    SYNONYM             VALID

Testing the output.

SQL> select count(*) from SYS_EXPORT_TABLE_01;
select count(*) from SYS_EXPORT_TABLE_01
*
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL>

Solution:

Drop the synonym SYS_EXPORT_TABLE_01.

SQL> drop public synonym SYS_EXPORT_TABLE_01;

synonym dropped.

Now lets check the export backup.

0283(oracle)[ORCL]:/home/oracle> s=CLICKHR_INTERNAL_TCPS.PAGE, CLICKHR_INTERNAL_TCPS.TEMPLATE                                                   <

Export: Release 11.2.0.2.0 - Production on Mon Nov 12 13:58:33 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=EXPORT_BACKUP_DIR:expdp_tables_ORACL.dmp logfile=EXPORT_BACKUP_DIR:expdp_tables_ORACL.log tables=CLICKHR_INTERNAL_TCPS.PAGE, CLICKHR_INTERNAL_TCPS.TEMPLATE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CLICKHR_INTERNAL_TCPS"."PAGE"              127.5 KB      19 rows
. . exported "CLICKHR_INTERNAL_TCPS"."TEMPLATE"          115.4 KB      19 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/dbexp/u01/oradata/export/expdp_tables_ORCL.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 13:59:05

0283(oracle)[ORCL]:/home/oracle> exit

Well that’s it, clean export backup.

DataPump Import Results In ORA-39001 Invalid Argument Value ORA-1775 Looping Chain Of Synonyms [ID 459151.1]

Advertisements