Home » SQL & PL/SQL » SQL & PL/SQL » Getting ORA : 00905 while synonym creation (Oracle 10G, Windows XP)
Getting ORA : 00905 while synonym creation [message #594356] Tue, 27 August 2013 17:09 Go to next message
my_oracle
Messages: 3
Registered: August 2013
Location: India
Junior Member
I am trying to execute the following query as SYSDBA :

create or replace public synonym my_con for sys.mytable

I have granted all priviledges on the table named mytable (exists in SYS schema) to the schema named my_schema.
And the above query works well. I can access the synonym from my_schema.

But if I try to execute the following query (either as sysdba or as myschema)
I get error - ORA : 00905 : Missing keyword

create or replace public synonym my_schema.my_con for sys.mytable

Re: Getting ORA : 00905 while synonym creation [message #594358 is a reply to message #594356] Tue, 27 August 2013 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 21940
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Getting ORA : 00905 while synonym creation [message #594370 is a reply to message #594356] Wed, 28 August 2013 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Getting ORA : 00905 while synonym creation [message #594372 is a reply to message #594356] Wed, 28 August 2013 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
create or replace public synonym my_schema.my_con for sys.mytable


Don't you think that create a PUBLIC synonym and give a schema name (MY_SCHEMA) is somewhat inconsistent? Either it is public, either it is in a schema.

Regards
Michel

[Edit: english... I think...]

[Updated on: Wed, 28 August 2013 04:41]

Report message to a moderator

Re: Getting ORA : 00905 while synonym creation [message #594374 is a reply to message #594356] Wed, 28 August 2013 01:16 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
my_oracle wrote on Tue, 27 August 2013 23:09

create or replace public synonym my_con for sys.mytable.


Have you created a non system table in the sys schema? Terrible idea. Don't do that. If the table to which you refer is not a non system table, then ignore this post


[EDITED by LF: fixed [quote] tags]

[Updated on: Wed, 28 August 2013 01:24] by Moderator

Report message to a moderator

Re: Getting ORA : 00905 while synonym creation [message #594387 is a reply to message #594372] Wed, 28 August 2013 02:57 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 28 August 2013 06:51

Don't you think that create a PUBLIC synonym and giving of schema name (MY_SCHEMA) is somewhat inconsistent? Either it is public, either it is in a schema.

No. The synonym is public, the object it points to must be in a schema.
I suspect the problem is due to the fact that the table is in sys.
Re: Getting ORA : 00905 while synonym creation [message #594389 is a reply to message #594387] Wed, 28 August 2013 03:02 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Wed, 28 August 2013 08:57
Michel Cadot wrote on Wed, 28 August 2013 06:51

Don't you think that create a PUBLIC synonym and giving of schema name (MY_SCHEMA) is somewhat inconsistent? Either it is public, either it is in a schema.

No. The synonym is public, the object it points to must be in a schema.
I think that what Michel is getting at is the definition:
create or replace public synonym [b]my_schema[/b].my_con for sys.mytable 


Re: Getting ORA : 00905 while synonym creation [message #594390 is a reply to message #594389] Wed, 28 August 2013 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're right. Didn't read the initial post carefully enough, thought he was executing the first command and getting an issue.
Indeed that makes no sense.
Re: Getting ORA : 00905 while synonym creation [message #594391 is a reply to message #594390] Wed, 28 August 2013 03:18 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
Yeah, it wasn't until Michel's comment that I properly read the second bit of code. Doesn't change either your or my opinion that the object being is sys is likely to be an issue too.
Re: Getting ORA : 00905 while synonym creation [message #594432 is a reply to message #594391] Wed, 28 August 2013 07:46 Go to previous messageGo to next message
my_oracle
Messages: 3
Registered: August 2013
Location: India
Junior Member
I got the syntax from http://docs.oracle.com

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
[ schema. ]synonym
FOR [ schema. ]object [ @ dblink ] ;


I also tried to execute the query as my_schema:
create or replace public synonym scott.load for loaded;

I have granted all privileges on the table loaded (exists in my_schema)to the schema Scott.
But still getting the error ORA:00905 - missing keyword.
I think the syntax is correct, then why I am getting this error.

In spite of scott.load if I write only load in the below code, and run it as my_schema then it works fine.
create or replace public synonym load for loaded;
OR
create or replace public synonym load for my_schema.loaded;


Re: Getting ORA : 00905 while synonym creation [message #594434 is a reply to message #594432] Wed, 28 August 2013 07:59 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
You still haven't shown anyone your actual execution of the command. I just don't believe anything you say that you did until I see an output of a full session.
Re: Getting ORA : 00905 while synonym creation [message #594435 is a reply to message #594432] Wed, 28 August 2013 08:00 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
I'm glad that you brought up the documentation. It would seem that you haven't read all of the documentation on the subject. It quite clearly states:
Quote:
schema

Specify the schema to contain the synonym. If you omit schema, then Oracle Database creates the synonym in your own schema. You cannot specify a schema for the synonym if you have specified PUBLIC.
Re: Getting ORA : 00905 while synonym creation [message #594465 is a reply to message #594435] Wed, 28 August 2013 15:23 Go to previous message
my_oracle
Messages: 3
Registered: August 2013
Location: India
Junior Member
Thanks a lot.
Now I understand the mistake.
Previous Topic: Value for the closest or equal date
Next Topic: Migration of three tables
Goto Forum:
  


Current Time: Thu Apr 17 15:00:35 CDT 2014

Total time taken to generate the page: 0.08989 seconds