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

Question

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';

Convocation

Answer 1

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.

Sigmoid

Answer 2

In Addition to @DCookie

If you want to create synonym of remote DB.

CREATE PUBLIC DATABASE LINK rmt CONNECT TO user IDENTIFIED BY pw USING 
'HOST_NAME:PORT/SERVICE_NAME_SID'

Dotterel