Help - Search - Members - Calendar
Full Version: *ORA-01775:
Oracle DBA Forums > Oracle > Oracle Forum
CHRIS_PENA
ORA-01775: looping chain of synonyms

how can i resolve this problem???
Gurpreet
>>ORA-01775: looping chain of synonyms
May be a synonym was defined that referred to itself. For example, the following definitions are circular:
CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1
Change one synonym definition so that it applies to a base table or view and retry the operation.

or send us Create Synonym statements.


Gurpreet S. Gill
burleson
Hi Chris,

My, you get interesting issues! In this case, you have a sunonym that refers to itself:

ORA-01775: looping chain of synonyms

Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1

Action: Change one synonym definition so that it applies to a base table or view and retry the operation.
CHRIS_PENA
yes, im doing a migration though, smile.gif anyway, is there a sql script wherein I can trace the redundant synonym or the looping synonym error??


thanks,
chris
CHRIS_PENA
QUOTE (burleson @ Mar 9 2007, 01:30 PM) *
Hi Chris,

My, you get interesting issues! In this case, you have a sunonym that refers to itself:

ORA-01775: looping chain of synonyms

Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:

CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1

Action: Change one synonym definition so that it applies to a base table or view and retry the operation.



thanks! problem solved....
tamio
Hello,
I have the ORA-01775 error, but I'm not able to drop any synonyms at all ( all give back the same ORA-01775 error ) so I can't remove the cyclic references.
Whats more, after creating a new table and synonym I'm not able to drop the synonym for that either !
Help please.


QUOTE (CHRIS_PENA @ Mar 13 2007, 10:50 AM) *
thanks! problem solved....
HAL9000
"after creating a new table and synonym I'm not able to drop the synonym for that either !"

Sounds like a self-referencing synonym. This ORA-01775 error is returned when the underlying object, bearing the same name as the synonym, is either renamed or dropped.

Instead of dropping them, try to alter them to apply to a base table or view and make sure it not self-referencing

Also run diagnostic scripts against dba_synonyms to chase the synonym hierarchy and find the problem. If you don't want to write your own, you can get one in the Oracle script collection:

http://www.oracle-script.com
tamio
Hello,
I altered the synonyms, and thats gotten rid of ORA-01775. However I'm now getting this error instead (in the alert log) )RA-04063 package body SYSMAN.EMD_MAINTENANCE has errors and ORA-06508 PL/SQL could not find program unit being called SYSMAN.EMD_MAINTENANCE
All of the synonyms previously modified in error were for non-system tables, other than dual.
I tried recompiling the above package body (from the prompt) but just got back an Altered with compilation errors message. How do I get to see what these actual error messages are, and how do I get the package back to a running state.
Moreover, whatever could have happened to upset a system owned package ?
Thanks in advance.


QUOTE (HAL9000 @ Jul 21 2008, 04:18 PM) *
"after creating a new table and synonym I'm not able to drop the synonym for that either !"

Sounds like a self-referencing synonym. This ORA-01775 error is returned when the underlying object, bearing the same name as the synonym, is either renamed or dropped.

Instead of dropping them, try to alter them to apply to a base table or view and make sure it not self-referencing

Also run diagnostic scripts against dba_synonyms to chase the synonym hierarchy and find the problem. If you don't want to write your own, you can get one in the Oracle script collection:

http://www.oracle-script.com
HAL9000
"ORA-04063 package body SYSMAN.EMD_MAINTENANCE has errors"

You were right to recompile the objects.

"Altered with compilation errors message."

When you get this message, issue the "show errors" command to see the exact errors.

It's a bug! Here it is:

http://highload.blogspot.com/2008/03/errro...enterprise.html

"SYSMAN.EMD_MAINTENANCE package error and the upgrade repository failed. Even if manually recreate the database encounter problem when dropping the sysman user as in metalink note 433419.1 . . .

NOTE: All the statements below should be run as the 'SYSMAN' user (Repository owner).

1. Run the partition maintenance routines

In order to run the partition maintenance routines, the OMS's will have to be stopped.
After all the OMS's have been shutdown, perform the following steps:

CODE
  SQL> EXEC emd_maintenance.remove_em_dbms_jobs;
  SQL> ALTER SYSTEM SET job_queue_processes=0 SID='*';
  SQL> EXEC emd_maintenance.partition_maintenance;
  SQL> ALTER SYSTEM SET job_queue_processes=10 SID='*';
  SQL> EXEC emd_maintenance.submit_em_dbms_jobs;


2. Clean up all pending-delete ECM snapshots

Delete all pending-delete ECM snapshots, by running these two PL/SQL routines:

CODE
    DECLARE
    pcb_params MGMT_PURGE_CALLBACK_PARAMS :=
      MGMT_PURGE_CALLBACK_PARAMS(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
  BEGIN
    ecm_ct.DELETE_SNAPSHOTS(pcb_params);
  END;
  /

  DECLARE
    pcb_params MGMT_PURGE_CALLBACK_PARAMS :=
      MGMT_PURGE_CALLBACK_PARAMS(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
  BEGIN
    ecm_ct.DELETE_HOST_CONFIG_SNAPSHOTS(pcb_params);
  END;
  /


3. After running all the cleanup, make sure the statistics on all the repository tables are accurate and up-to-date. Run this procedure to analyze the statistics on the repository tables:

CODE
    SQL> EXEC emd_maintenance.analyze_emd_schema('SYSMAN');


Remember to install the patch required before proceeding, mine is Patch 4329444 SKIP SCAN OF COMPRESSED INDEX ON INDEX-ORGANIZED TABLE (IOT) GIVES WRONG RESULTS
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.