[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

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

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

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

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

相關文章