Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRPpx-0003v5-L5 for pgsql-sql@arkaria.postgresql.org; Wed, 02 Oct 2013 17:03:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VRPpw-0000Jw-TJ for pgsql-sql@arkaria.postgresql.org; Wed, 02 Oct 2013 17:03:44 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRPpw-0000Jm-4Y; Wed, 02 Oct 2013 17:03:44 +0000 Received: from s2exchange2.hq.s2sys.com ([23.25.203.146]) by makus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VRPps-0005ry-IO; Wed, 02 Oct 2013 17:03:43 +0000 Received: from S2EX10.hq.s2sys.com ([192.168.0.121]) by s2exchange2.hq.s2sys.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 2 Oct 2013 13:05:21 -0400 Received: from S2EX10.hq.s2sys.com ([::1]) by S2EX10.hq.s2sys.com ([::1]) with mapi id 14.01.0218.012; Wed, 2 Oct 2013 13:03:24 -0400 From: Chris Twombly To: Albe Laurenz , Bhanu Murthy , "pgsql-admin@postgresql.org" , "pgsql-sql@postgresql.org" Subject: Re: [ADMIN] DB link from postgres to Oracle; how to query Dbname.tablename? Thread-Topic: [ADMIN] DB link from postgres to Oracle; how to query Dbname.tablename? Thread-Index: AQHOvwAZKRP1v7tHhECaGmJhMWtAmJnhT46AgABUsMA= Date: Wed, 2 Oct 2013 17:03:24 +0000 Message-ID: <16E7B47C74F4CA47918704C70A57611B2FE979@S2EX10.hq.s2sys.com> References: <524A90D1.5000109@iqbuzz.ru> <524B22C7.2010300@pasteleros.org.ar> <1380670916.20112.YahooMailNeo@web164504.mail.gq1.yahoo.com>, In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [209.6.114.241] Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginalArrivalTime: 02 Oct 2013 17:05:21.0265 (UTC) FILETIME=[93E38E10:01CEBF91] X-Pg-Spam-Score: -0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org Do keep in mind that querying across databases generally garners really poo= r performance, and can bring=20 your application to its knees with astonishing speed. ________________________________________ From: pgsql-admin-owner@postgresql.org [pgsql-admin-owner@postgresql.org] o= n 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.t= ablename? 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" identifie= d by "blahblah" using > 'postgresql'; > Since Postgres does not support public synonyms across databases in a clu= ster, how do I connect to a > specific database and query a specific table in this Postgres cluster usi= ng 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 c= alled table01 from pgdb01 > database? > > Even though the table user_account exists in pgdb01 database, I cannot se= lect 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 --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql