Home » SQL & PL/SQL » SQL & PL/SQL » script to create synonym for all table of schema (oracle 9i , solaris)
script to create synonym for all table of schema [message #357738] Thu, 06 November 2008 07:19 Go to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Hi All,
I trying to create synonym for many tables on my schema.
I' m looking for script to do that.

thanks

koff
Re: script to create synonym for all table of schema [message #357741 is a reply to message #357738] Thu, 06 November 2008 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And where is your problem in trying to achieve this?
In other words, post what you tried.

Regards
Michel
Re: script to create synonym for all table of schema [message #357782 is a reply to message #357741] Thu, 06 November 2008 09:48 Go to previous messageGo to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Michel Cadot wrote on Thu, 06 November 2008 14:34
And where is your problem in trying to achieve this?
In other words, post what you tried.

Regards
Michel




just create synonym for all tables ( 170 tables )on schema A for
schema B to grant select, insert, update, and delete privileges to user A on schema B

I know I can do :
create synonym A.table1 for B.table1
create synonym A.table2 for B.table2
create synonym A.table3 for B.table3
.......

But it's very tired manually to do this for 170 tables located on schema B
so I'm trying to write a script to do that.

thanks
best regards
koff
Re: script to create synonym for all table of schema [message #357784 is a reply to message #357782] Thu, 06 November 2008 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use select on user_tables to generate the statements.

Regards
Michel
Re: script to create synonym for all table of schema [message #357792 is a reply to message #357738] Thu, 06 November 2008 10:21 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi
If you are using TOAD(Quest software),you can do it using the
menu 'Create' then 'Synonym' and check the box 'Show multisynonym
grid'. Here you can create many synonyms at a time
Re: script to create synonym for all table of schema [message #357810 is a reply to message #357792] Thu, 06 November 2008 13:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Which option would, in the long run, be more helpful to the original poster in your opinion? Learning how to write such a script or learning how to click some options in a tool that might not be available next time he needs it?
Re: script to create synonym for all table of schema [message #357811 is a reply to message #357810] Thu, 06 November 2008 13:28 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,
Tools are the means of making the lengthy and complex works simpler. As far as I know, this option is always available.
I feel working smart is better than working hard.
Anyway i'll post a different solution for the poster.
Re: script to create synonym for all table of schema [message #357813 is a reply to message #357811] Thu, 06 November 2008 14:03 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As far as I know, this option is always available.

Wrong! In many entreprises TOAD is not allowed.
Often, the only available tool is SQL*Plus.

You must learn to work with it and do all things with it.
Then and only then, if something else easier is available you can use it.
But trust me, when you master SQL*Plus, you no more need another tool.

Regards
Michel
Previous Topic: Exists/Not Exists in the same query
Next Topic: ORA-00997: illegal use of LONG datatype
Goto Forum:
  


Current Time: Tue Dec 06 16:24:28 CST 2016

Total time taken to generate the page: 0.05728 seconds