[20190102]ORA-01775 looping chain of synonyms.txt

[20190102]ORA-01775 looping chain of synonyms.txt --//别人的系统导出遇到ora-01775错误,这个问题以前也遇到过.重复演示出来,主要问题在于用户以前导出异常中断, --//里面存在SYS_IMPORT_SCHEMA_01表,并且给这个表建立同义词.以后维护中删除SYS_IMPORT_SCHEMA_01表.这样再使用 --//导入操作时就会遇到这个问题. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> desc SYS_EXPORT_TABLE_01 ERROR: ORA-04043: object SYS_EXPORT_TABLE_01 does not exist SCOTT@book> create public synonym SYS_EXPORT_TABLE_01 for SYS_EXPORT_TABLE_01; Synonym created. SCOTT@book> desc SYS_EXPORT_TABLE_01 SP2-0749: Cannot resolve circular path of synonym "SYS_EXPORT_TABLE_01" --//oracle设计存在一些缺陷,即使对象不存在可以建立这样的同义词. SCOTT@book> desc SYS_IMPORT_TABLE_01 ERROR: ORA-04043: object SYS_IMPORT_TABLE_01 does not exist SCOTT@book> create public synonym SYS_IMPORT_TABLE_01 for SYS_IMPORT_TABLE_01; Synonym created. SCOTT@book> desc SYS_IMPORT_TABLE_01 SP2-0749: Cannot resolve circular path of synonym "SYS_IMPORT_TABLE_01" 2.导出: $ expdp scott/book tables=depty Export: Release 11.2.0.4.0 - Production on Wed Jan 2 15:37:06 2019 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, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a***** tables=depty Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."DEPTY"                             5.968 KB       6 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /u01/app/oracle/admin/book/dpdump/expdat.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 2 15:37:12 2019 elapsed 0 00:00:05 --//导出没有问题. SCOTT@book> rename  depty to deptz; Table renamed. 3.导入: $ impdp scott/book tables=depty Import: Release 11.2.0.4.0 - Production on Wed Jan 2 15:40:05 2019 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, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-01775: looping chain of synonyms --//导入出现ora-01775错误. $ oerr ora 1775 01775, 00000, "looping chain of synonyms" // *Cause: // *Action: 4.跟踪看看: SYS@book> alter system set events '1775 trace name ERRORSTACK level 3'; System altered. $ impdp scott/book tables=depty .... $ grep -i ora-01775 *.trc book_ora_61255.trc:ORA-01775: looping chain of synonyms --//检查跟踪文件可以发现如下: dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ORA-01775: looping chain of synonyms ----- Current SQL Statement for this session (sql_id=4mbjyfu9mumx7) ----- SELECT COUNT(*) FROM SYS_IMPORT_TABLE_01 ----- PL/SQL Stack ----- ----- PL/SQL Call Stack -----   object      line  object   handle    number  name 0x7e2d2aa8       158  package body SYS.DBMS_DATAPUMP 0x7e2d2aa8      5213  package body SYS.DBMS_DATAPUMP 0x7cf39980         1  anonymous block SCOTT@book> SELECT COUNT(*) FROM SYS_IMPORT_TABLE_01; SELECT COUNT(*) FROM SYS_IMPORT_TABLE_01                      * ERROR at line 1: ORA-01775: looping chain of synonyms --//感觉先检查这个表.结果报错. SCOTT@book> select * from dba_objects where object_name='SYS_IMPORT_TABLE_01'; OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S NAMESPACE EDITION_NAME ------ -------------------- ---------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - --------- ------------- PUBLIC SYS_IMPORT_TABLE_01                  90566                SYNONYM     2019-01-02 15:38:28 2019-01-02 15:41:03 2019-01-02:15:41:03 VALID   N N N         1 SCOTT@book> drop public synonym SYS_IMPORT_TABLE_01 ; Synonym dropped. SYS@book> alter system set events '1775 trace name errorstack off'; System altered. $ impdp scott/book tables=depty Import: Release 11.2.0.4.0 - Production on Wed Jan 2 15:47:43 2019 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, OLAP, Data Mining and Real Application Testing options Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/a****** tables=depty Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."DEPTY"                             5.968 KB       6 rows Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 2 15:47:47 2019 elapsed 0 00:00:03