Home » SQL & PL/SQL » SQL & PL/SQL » Accessing a table from a different database (oracle 10g,windows)
Accessing a table from a different database [message #340305] Tue, 12 August 2008 04:48 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

How can we access a table which is in different database.
for e.g., Table "X" is present in Database name: "P1".
Now , I want to access this table from database "P2" .
Both P1 and P2 are oracle databases only.
how can I achieve this.

Thanks,
Re: Accessing a table from a different database [message #340306 is a reply to message #340305] Tue, 12 August 2008 04:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
By using a Database Link
Re: Accessing a table from a different database [message #340330 is a reply to message #340305] Tue, 12 August 2008 06:22 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks,

I created a DBlink and I am using it for select/query the data in one of the Procedure.but,will it have any performance overhead?

Which is the best way
1)Querying the datbase from the JDBC and retrive the results
or 2)by using the dblink ?

Thanks,

Re: Accessing a table from a different database [message #340341 is a reply to message #340330] Tue, 12 August 2008 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It all depends on what you finally want to do.

Regards
Michel
Re: Accessing a table from a different database [message #340365 is a reply to message #340305] Tue, 12 August 2008 08:33 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks Michel,
But which method you suggest?
Re: Accessing a table from a different database [message #340366 is a reply to message #340365] Tue, 12 August 2008 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 12 August 2008 14:16
It all depends on what you finally want to do.

Regards
Michel


Re: Accessing a table from a different database [message #340372 is a reply to message #340305] Tue, 12 August 2008 08:40 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Actually I have to suggest which is the best way?
what happens if we use dblink ?
performance will be degrade ,right?
If that is the case then we will opt 2 method.
Re: Accessing a table from a different database [message #340375 is a reply to message #340372] Tue, 12 August 2008 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ramanajv1968 wrote on Tue, 12 August 2008 06:40
Actually I have to suggest which is the best way?
what happens if we use dblink ?
performance will be degrade ,right?
If that is the case then we will opt 2 method.


so benckmark all & identify the fastest
Re: Accessing a table from a different database [message #340377 is a reply to message #340375] Tue, 12 August 2008 09:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If one method was always better, why wouldn't we say so?
The optimum solution depends entirely on what you're doing.
Re: Accessing a table from a different database [message #340426 is a reply to message #340330] Tue, 12 August 2008 16:13 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you might also consider use of materialized view (if possible); doing so, you'd have all data in your local database and queries might run quite fast (if compared to ones that run over a database link).
Previous Topic: External table query
Next Topic: External table does not allow nulls by default
Goto Forum:
  


Current Time: Sat Dec 10 16:33:06 CST 2016

Total time taken to generate the page: 0.17463 seconds