[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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2287166/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2287166/