Solved

How to create PRIVATE SYNONYM ?

Posted on 2006-11-08
6
5,024 Views
Last Modified: 2010-08-05
Hello,

I have created PUBLIC SYNONYM for tables on a schema without problems.

However, the DBA told me that it's better to create PRIVATE SYNONYM for tables to an specific user.

After dropping PUBLIC SYNONYMs, I tried to do this, but got error:

CREATE PRIVATE SYNONYM Table_1   for MySchema.Table_1 ;

Error: ORA-00922: missing or invalid option

How could I create private synonym for an database user: My_Read_User
0
Comment
Question by:miyahira
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17898229
just
CREATE SYNONYM
there's no PRIVATE keyword. But this will be private in fact.
0
 
LVL 1

Author Comment

by:miyahira
ID: 17898296
Mmmm... If I try to do:

CREATE SYNONYM Table_1   for MySchema.Table_1 ;

Then got error:
ORA-01471: cannot create a synonym with same name as object

So I do:

CREATE SYNONYM Table_1x   for MySchema.Table_1 ;

And it's OK.

However, when I connect to database as My_Read_User, I have to query like this:
SELECT * FROM MySchema.Table_1x

Is there anyway to connect to database as My_Read_User and query without specifying any schema:
SELECT * FROM Table_1
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 80 total points
ID: 17898309
Yes -
CREATE SYNONYM Table_1   for MySchema.Table_1 ;

but run this as My_Read_user
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 35 total points
ID: 17898322
create synonym table_1 for myschema.mytable_11082006; => often private synonyms are used create generic names for more complicated ones.

grant select on table_1 to my_read_user;

0
 
LVL 1

Author Comment

by:miyahira
ID: 17898340
Ok, I got it... thanks GGuzdziol
0
 
LVL 7

Assisted Solution

by:Stephen Lappin
Stephen Lappin earned 35 total points
ID: 17898347
You cannot create a (private) synonym with the same name as an object in the schema you are connected to.

You CAN create a (private) synonym for an object in a different schema with teh same name.

You can also create a PUBLIC synonym for an object in any schema using the same name as the object.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
pl/sql - query very slow 26 105
Oracle function to insert records? 15 81
Excess Redo 3 47
Using Sum with Case When within a query 9 44
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question