script to create synonym for all table of dblink [message #434900] |
Fri, 11 December 2009 10:32  |
koff10
Messages: 58 Registered: December 2006 Location: france
|
Member |
|
|
Hi All,
I'm trying to build script to create synonym from dblink.
I can create one by one synonym from dblink as below.
1) create database link db_lnk connect to user identified by pwd
using 'alias';
2) create synonym table1 for table1@db_lnk;
3) create synonym table2 for table2@db_lnk;
But the matter is about many tables in my dblink ?
so I'm looking for help to generate script to do the job.
Thanks for all
|
|
|
|
|
|
|
|
|
Re: script to create synonym for all table of dblink [message #435314 is a reply to message #435140] |
Tue, 15 December 2009 09:42   |
koff10
Messages: 58 Registered: December 2006 Location: france
|
Member |
|
|
Michel Cadot wrote on Mon, 14 December 2009 17:28Quote:But create synonym do work fine :
create synonym table1 for table1@db_link[/email];
You can ALWAYS create a synonym, this does not mean you can access the target object nor it exists.
SQL> create synonym x for non_existent@not_existent;
Synonym created.
Regards
Michel
Hi Michel,
Your query below is OK .
"select table_name from user_tables@db_link;" .
Actually my db_link is about synonyms not tables .
so "select synonym_name from user_synonyms@db_link;"
works fine .
Then I can use this query below to create script.
select 'CREATE synonym '||synonym_name || ' FOR ' || SYNONYM_NAME || '@DB_LINK[/email][/email][/email];' from user_synonyms@db_link[/email][/email][/email];
Thanks for ALL
[Updated on: Tue, 15 December 2009 10:35] by Moderator Report message to a moderator
|
|
|
|