Re: [ADMIN] DB link from postgres to Oracle; how to query Dbname.tablename?

Do keep in mind that querying across databases generally garners really poor performance, and can bring
your application to its knees with astonishing speed.

________________________________________
From: pgsql-admin-owner(at)postgresql(dot)org [pgsql-admin-owner(at)postgresql(dot)org] on behalf of Albe Laurenz [laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: Wednesday, October 02, 2013 3:58 AM
To: Bhanu Murthy; pgsql-admin(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
Subject: Re: [ADMIN] DB link from postgres to Oracle; how to query Dbname.tablename?

Bhanu Murthy wrote:
> Using Oracle Heterogeneous Services (Oracle HS) I have configured/created a DB link from Postgres 9.3
> database into Oracle 11gR3 database (with postgres DB user credentials).
>
> SQL> create public database link pg_link connect to "postgres" identified by "blahblah" using
> 'postgresql';
> Since Postgres does not support public synonyms across databases in a cluster, how do I connect to a
> specific database and query a specific table in this Postgres cluster using the HS DB link?
>
> Let's say, if I have 2 Postgres databases named pgdb01 and pgdb02 in the Postgres cluster, using this
> DB link that I have created in Oracle, how can I query a specific table called table01 from pgdb01
> database?
>
> Even though the table user_account exists in pgdb01 database, I cannot select from it using the DB
> link.

You have to connect to a database to query its data.

Rather than defining a database link to database "postgres",
define two links, one for "pgdb01" and one for "pgdb02".

Yours,
Laurenz Albe

--
Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin