Home » SQL & PL/SQL » SQL & PL/SQL » script to create synonym for all table of dblink (oracle 9i , solaris)
script to create synonym for all table of dblink [message #434900] Fri, 11 December 2009 10:32 Go to next message
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 #434901 is a reply to message #434900] Fri, 11 December 2009 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select table_name from user_tables@db_link;

Regards
Michel
Re: script to create synonym for all table of dblink [message #434904 is a reply to message #434900] Fri, 11 December 2009 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
select 'CREATE SYNONYM '||table_name || ' FOR ' || TABLE_NAME || '@DB_LINK;' from user_tables@db_link;
Re: script to create synonym for all table of dblink [message #435137 is a reply to message #434901] Mon, 14 December 2009 10:23 Go to previous messageGo to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Michel Cadot wrote on Fri, 11 December 2009 17:35
select table_name from user_tables@db_link;

Regards
Michel



Hi,
Thanks for your reply.
There are no ROWS about your query:
select table_name from user_tables@db_link ;

It's like I cannot get tables through my db_link.


But create synonym do work fine :
create synonym table1 for table1@db_link

regards
koff
Re: script to create synonym for all table of dblink [message #435139 is a reply to message #435137] Mon, 14 December 2009 10:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
There are no ROWS about your query:
select table_name from user_tables@db_link ;


Are any tables actually owned by the schema that you are connecting to across the db link, or does that user simply see public/private synonyms?
Re: script to create synonym for all table of dblink [message #435140 is a reply to message #435137] Mon, 14 December 2009 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But create synonym do work fine :
create synonym table1 for table1@db_link;

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

[Updated on: Tue, 15 December 2009 10:33]

Report message to a moderator

Re: script to create synonym for all table of dblink [message #435152 is a reply to message #435140] Mon, 14 December 2009 13:30 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try selecting from all_tables, not user_tables. all_tables is all the ones you have access to (allows for remote synonyms).

select 'CREATE SYNONYM '||table_name || ' FOR ' || TABLE_NAME || '@DB_LINK;' from ALL_tables@db_link; 
Re: script to create synonym for all table of dblink [message #435314 is a reply to message #435140] Tue, 15 December 2009 09:42 Go to previous messageGo to next message
koff10
Messages: 58
Registered: December 2006
Location: france
Member
Michel Cadot wrote on Mon, 14 December 2009 17:28
Quote:
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

Re: script to create synonym for all table of dblink [message #435320 is a reply to message #435314] Tue, 15 December 2009 10:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, your original question was very much about tables:Quote:

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 ?


But if you've got the answer you need, then that's all that matters.
Previous Topic: Jobs running twice
Next Topic: Please help with ORA-01427: single-row subquery returns more than one row
Goto Forum:
  


Current Time: Sat Oct 01 05:56:51 CDT 2016

Total time taken to generate the page: 0.14244 seconds