ORA-1775 Reported When Compiling Package Body

刘泽宇

现象：
Whilst a specific case is detailed here , please follow this following analysis to establish whether you may be encountering
the same root issue .


1. When applying the 11.2.0.4. patchset and running upgrade script, the following is observed :

...
SQL> CREATE OR REPLACE PACKAGE BODY sys.dbms_logmnr_internal wrapped
...
766  /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY SYS.DBMS_LOGMNR_INTERNAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-01775: looping chain of synonyms

2.  There are no dependent synonyms for this object :

select owner,object_name,object_id,object_type,status from dba_objects where object_id in ( select p_OBJ# from DEPENDENCY$ where d_obj# = (select object_id from dba_objects where object_name like 'DBMS_LOGMNR_INTERNAL' and object_type = 'PACKAGE BODY'));

3. "hcheck.sql" script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g - Doc id  136697.1

On running this script , the following is reported in the output :

...

HCKW-0018: OBJECT name clashes with SCHEMA name    <<<<
Schema=SYSTEM Object=PUBLIC.SYSTEM (SYNONYM)     

原因：
There is a public synonym with name SYSTEM

解决方法：
Drop the public synonym SYSTEM and repeat the activity.

Also do check for other Invalid Public Synonyms  in general.