| Getting ORA : 00905 while synonym creation [message #594356] |
Tue, 27 August 2013 17:09  |
 |
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 #594374 is a reply to message #594356] |
Wed, 28 August 2013 01:16  |
pablolee
Messages: 2882
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 |
cookiemonster
Messages: 13968
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 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
cookiemonster wrote on Wed, 28 August 2013 08:57Michel 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 #594435 is a reply to message #594432] |
Wed, 28 August 2013 08:00 |
pablolee
Messages: 2882
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
