From: Chris Twombly <CTwombly@s2sys.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Bhanu Murthy <bhanu_murthy@yahoo.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: [ADMIN] DB link from postgres to Oracle; how to query Dbname.tablename?
Date: Wed, 2 Oct 2013 17:03:24 +0000
Message-ID: <16E7B47C74F4CA47918704C70A57611B2FE979@S2EX10.hq.s2sys.com> (raw)
In-Reply-To: <A737B7A37273E048B164557ADEF4A58B17C25DEB@ntex2010a.host.magwien.gv.at>
References: <524A90D1.5000109@iqbuzz.ru>
<CABUevEz3E8zyUVgGw99hbpVNW-z8CP0DsE2y0mNvGWrN8g+fVQ@mail.gmail.com>
<524B22C7.2010300@pasteleros.org.ar>
<1380670916.20112.YahooMailNeo@web164504.mail.gq1.yahoo.com>
<A737B7A37273E048B164557ADEF4A58B17C25DEB@ntex2010a.host.magwien.gv.at>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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@postgresql.org [pgsql-admin-owner@postgresql.org] on behalf of Albe Laurenz [laurenz.albe@wien.gv.at]
Sent: Wednesday, October 02, 2013 3:58 AM
To: Bhanu Murthy; pgsql-admin@postgresql.org; pgsql-sql@postgresql.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: CTwombly@s2sys.com, laurenz.albe@wien.gv.at, bhanu_murthy@yahoo.com, pgsql-admin@postgresql.org
Subject: Re: [ADMIN] DB link from postgres to Oracle; how to query Dbname.tablename?
In-Reply-To: <16E7B47C74F4CA47918704C70A57611B2FE979@S2EX10.hq.s2sys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox