querying over multiple database links

#1

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@dblink_to_remote1

I have another local database db1 with database link to remote2 =>
@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

#2

On Jul 5, 4:01*pm, Krzysztof Cierpisz <ciape... (AT) gmail (DOT) com> wrote:

Quote:

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@dblink_to_remote1

I have another local database db1 with database link to remote2 =
@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>@<remote_database>;

HTH
-g

#3

On Jul 5, 6:01*pm, gazzag <justif... (AT) jamms (DOT) org> wrote:

Quote:

On Jul 5, 4:01*pm, Krzysztof Cierpisz <ciape... (AT) gmail (DOT) 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@dblink_to_remote1

I have another local database db1 with database link to remote2 =
@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>@<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.

#4

On Jul 5, 5:20*pm, Krzysztof Cierpisz <ciape... (AT) gmail (DOT) com> wrote:

Quote:

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.- Hide quoted text -

- Show quoted text -

Apologies Krzysztof, I should have read your post more carefully!

Can you not create a database link direct to remote1 from your local
db using the same credentials as the database link from remote2 to
remote1?

HTH
-g

#5

On Jul 6, 11:54*am, gazzag <justif... (AT) jamms (DOT) org> wrote:

Quote:

Can you not create a database link direct to remote1 from your local
db using the same credentials as the database link from remote2 to
remote1?

Unfortunately I don't know credentials that were used for creating
that public link. I can use it and know the username with which it
connects but cannot replicate that db link on my local database.

--
chris

#6

On Wed, 06 Jul 2011 12:48:15 -0700, Krzysztof Cierpisz wrote:

Quote:

Unfortunately I don't know credentials that were used for creating that
public link. I can use it and know the username with which it connects
but cannot replicate that db link on my local database.

Actually, you can. If you retrieve DDL statement for the link by using
DBMS_METADATA, you will get "CONNECT TO <user> IDENTIFIED BY VALUES <...>
statement, which will enable you to re-create the link. Catch is that you
still need "select any catalog" privilege, in order to do that.

--
http://mgogala.byethost5.com

#7

On Jul 7, 1:45*am, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:

Quote:

On Wed, 06 Jul 2011 12:48:15 -0700, Krzysztof Cierpisz wrote:
Unfortunately I don't know credentials that were used for creating that
public link. I can use it and know the username with which it connects
but cannot replicate that db link on my local database.

Actually, you can. If you retrieve DDL statement for the link by using
DBMS_METADATA, you will get "CONNECT TO <user> IDENTIFIED BY VALUES <...
statement, which will enable you to re-create the link. Catch is that you
still need "select any catalog" privilege, in order to do that.

--http://mgogala.byethost5.com

yes, tried that already, but I don't have that priv.

--
chris

#8

On Thu, 07 Jul 2011 01:07:03 -0700, Krzysztof Cierpisz wrote:

Quote:

yes, tried that already, but I don't have that priv.

Well then, you should pay a visit to your friendly neighbourhood DBA, who
has all the privileges needed to do the job.

--
http://mgogala.byethost5.com

#9

On Jul 7, 3:07*pm, Mladen Gogala <mgog... (AT) no (DOT) address.invalid> wrote:

Quote:

On Thu, 07 Jul 2011 01:07:03 -0700, Krzysztof Cierpisz wrote:
yes, tried that already, but I don't have that priv.

Well then, you should pay a visit to your friendly neighbourhood DBA, who
has all the privileges needed to do the job.

Yeah, that's what I did. I got access to the target database so no
issues for now.
I was just wondering what joel mentioned
'table_name@<remote_database>'@<remote_database2> if that is
supported.

thanks,
chris