circular path of synonym

[ksridhar]

Hi ,

I am facing with the problem

SQL> desc dd_ec_le1_dates;
SP2-0749: Cannot resolve circular path of synonym "dd_ec_le1_dates"

If i use this statement

select count(*) from table_name

then i get the following error
ERROR at line 1:
ORA-00942: table or view does not exist

I am accessing this table from different schema.

for this problem i have dropped the public synonym for this table recreated it still the problem persists..

what is the problem i am unable to access the table even though i have created a public synonym?

Thanks
Sridhar

[marist89]

You most likely have a synonym that never resolves back to an actual object.

[ksridhar]

AS i have mentioned there was a Public synonym i have dropped and re created the public synonym .. the problem still persists

[patel_dil]

what does
select * from all_synonyms
show?

who is the owner of that table/view? What is the command you are using to create a SYNONYM for that table? does the synonym reference go away from all_synonyms tables once you drop the synonym? This is all I could think. No idea what else is goping wrong here.

[ksridhar]

Originally posted by patel_dil
what does
select * from all_synonyms
show?

who is the owner of that table/view? What is the command you are using to create a SYNONYM for that table? does the synonym reference go away from all_synonyms tables once you drop the synonym? This is all I could think. No idea what else is goping wrong here.

I can see the synonym in all_synonyms

I have created the table in a different schema

The command used for creating the synonym is
"CREATE PUBLIC SYNONYM FOR

It goes away from all_synonyms when i dropped the synonym..

[patel_dil]

Try just creating the Synonym for the user who needs access to that table. Avoid creating PUBLIC synonyms, atleast till your problem is resolved. and please post complete syntax.

[Partha]

Initially, login as the owner of the table and grant access to the user in whose schema the synonyms are to be created.

*********************************************************
connect as the synonym owner
Create synonym synonym_name for schema.tablename;

select * from synonym_name;

*********************************************************

Hope this will solve the problem.

Tks

Partha

(Njoy DBAing)

[thanigai]

simple steps..here
connect owner/password;

@create table/procedure hello..

grant execute on hello to 2user;

connect 2user/password;

create synonym hello for owner.hello;

exec hello;

hope it makes you better

Thanks.
Thanigaivasan.

[ksridhar]

The work around is
Dropping the Table and public synonym and recreating all

why does the error comes and what is the solution ?

[marist89]

Originally posted by ksridhar
The work around is
Dropping the Table and public synonym and recreating all

why does the error comes and what is the solution ?

The error comes because your synonym does not resolve to an object. The solution is to understand what you are doing before you go creating public synonyms all over the place.