Re: querying over multiple database links
From: joel garry <joel-garry_at_home.com>
Date: Wed, 6 Jul 2011 10:35:33 -0700 (PDT)
Message-ID: <aa249436-2724-4d92-adee-297734e4579d_at_f39g2000prb.googlegroups.com>
On Jul 5, 9:20 am, Krzysztof Cierpisz <ciape..._at_gmail.com> wrote:
> On Jul 5, 6:01 pm, gazzag <justif..._at_jamms.org> wrote:
>
>
>
> > On Jul 5, 4:01 pm, Krzysztof Cierpisz <ciape..._at_gmail.com> wrote:
>
> > > on database remote1 is a table I want to access => table1
> > > I have a very limited schema/user created on remote2.
>
> > > remote2 has public database link to remote1, so I can access
> > > table1_at_dblink_to_remote1
>
> > > I have another local database db1 with database link to remote2 =>
> > > _at_dblink_to_remote2
>
> > > I would like to create a table from remote1 on my local db1.
> > > I can achieve that with export/import (giving remote2 credentials).
>
> > > Is there a better solution for that task?
>
> > > thanks,
> > > chris
>
> > CREATE TABLE <table_name>
> > AS SELECT * FROM <table_name>_at_<remote_database>;
>
> > HTH
> > -g
>
> the table is not on <remote_database> -> I have no direct access to
> <remote_database>
> I have only indirect access over a db_link from "another" database to
> <remote_database>. I have a very limited access on this "another"
> database so I cannot create any objects there.
Date: Wed, 6 Jul 2011 10:35:33 -0700 (PDT)
Message-ID: <aa249436-2724-4d92-adee-297734e4579d_at_f39g2000prb.googlegroups.com>
On Jul 5, 9:20 am, Krzysztof Cierpisz <ciape..._at_gmail.com> wrote:
> On Jul 5, 6:01 pm, gazzag <justif..._at_jamms.org> wrote:
>
>
>
> > On Jul 5, 4:01 pm, Krzysztof Cierpisz <ciape..._at_gmail.com> wrote:
>
> > > on database remote1 is a table I want to access => table1
> > > I have a very limited schema/user created on remote2.
>
> > > remote2 has public database link to remote1, so I can access
> > > table1_at_dblink_to_remote1
>
> > > I have another local database db1 with database link to remote2 =>
> > > _at_dblink_to_remote2
>
> > > I would like to create a table from remote1 on my local db1.
> > > I can achieve that with export/import (giving remote2 credentials).
>
> > > Is there a better solution for that task?
>
> > > thanks,
> > > chris
>
> > CREATE TABLE <table_name>
> > AS SELECT * FROM <table_name>_at_<remote_database>;
>
> > HTH
> > -g
>
> the table is not on <remote_database> -> I have no direct access to
> <remote_database>
> I have only indirect access over a db_link from "another" database to
> <remote_database>. I have a very limited access on this "another"
> database so I cannot create any objects there.
It's been too long since I've done this, but I think what you want to do is desc <yourpublicsynonym>_at_<remove_database2> (or select * from user_db_links_at_<remote_database2>, then if you can see that, you have to make some bizarro usage with quotes and _at_ signs like desc 'table_name_at_<remote_database>'@<remote_database2>
But there is some other consideration I can't remember, you have to very carefully follow the syntax for dblinks. Something about the definition of the _at_ sign varies depending on its position in the redirect string. If I even got your dblinks straight.
I know I did something like this back in the 7.3/8.x days, but not without object creation access to the middle db. Performance is likely to be bad.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2011/jul/05/la-sheriff-pta-moms-cheated-investors-out-of-1m/Received on Wed Jul 06 2011 - 12:35:33 CDT