create synonym for table in a database hosted in a different server

oracle synonym netezza
chemicalkt picture chemicalkt · Jul 26, 2012 · Viewed 59.6k times · Source

In oracle, if the other database is within the same server, I did the following to create a synonym

CREATE SYNONYM synonymName FOR databaseName.schema.table;

What should I do to create a synonym for tables in a database hosted in a different server so that I could provide the server connection credentials.

I need this for both oracle and netezza.

Edit: While trying(for Oracle) by taking reference of the answer below, I got a syntax error when the remote link contains ip address or a '-' in the link name. eg.

CREATE PUBLIC DATABASE LINK abc-def.xyz.com CONNECT TO user IDENTIFIED BY pw USING 'databaseName';

Answer

DCookie picture DCookie · Jul 26, 2012

For Oracle, you would create a database link to the remote database:

CREATE PUBLIC DATABASE LINK rmt CONNECT TO user IDENTIFIED BY pw USING 'remotedb';

Then create the synonym:

CREATE SYNONYM syn FOR schema.table@rmt;

Then simply SELECT x,y,z FROM syn;

Not sure about netezza.

EDIT:

You cannot use a "-" character in a database link name.

If you choose to use an IP address in a link name, then you must surround the link name in double quotes. Not sure why you'd want to do this anyway - what if the IP address of your remote database changes? To me, the DB link name should be a mnemonic for the remote database. An IP address tells me little.

Related questions

How can I check that if synonym already exist then don't create synonym

I am using Oracle SQL developer 2.1 for creating a synonym. CREATE OR REPLACE SYNONYM "ETKS_PR_RW"."SQ_CLDOS_ATCHMNT_ID" FOR "CLDOS_ONLINE_DBA"."SQ_CLDOS_ATCHMNT_ID"; How can I check that if this synonym already exists then …

Read More
create synonym ora-01031 insufficient privileges

I need help understanding what grants/privileges a user needs to CREATE a SYNONYM when it points to another (different) schema object. When I try the below, I get ora-01031 insufficient privileges, so obviously I am missing and failing to …

Read More
ORA-00980 synonym translation no longer valid in PLSQL

I've got a synonym on a remote Oracle database that I can access in SQL over a database link, eg, insert into my_table select * from my_synonym@my_database_link; If I put the above statement into a PLSQL …

Read More