Home » SQL & PL/SQL » SQL & PL/SQL » Risks or disadvantages of using database links (Oracle 10g)
Risks or disadvantages of using database links [message #416614] Mon, 03 August 2009 09:19 Go to next message
akool
Messages: 2
Registered: August 2009
Junior Member
Hi,
We need to access the data from two different oracle databases in a web based application. But as the data is very large, we are planning to implement it at the database level. We are planning to create a materialized view based on 2 tables which are present in 2 different databases, using database link in the Mview definition.
We want to analyze all the sides of this approach, like the risks/dis-advantages and advatnages of using this approach.
Is there any other way to access the data from 2 different databases ??
Any suggestions or comments on this would be very helpful for us to deciede the approach.

Please help.

Thanks & Regards,
AK
Re: Risks or disadvantages of using database links [message #416629 is a reply to message #416614] Mon, 03 August 2009 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any other way to access the data from 2 different databases ??

From inside a database, no.

Quote:
Any suggestions or comments on this would be very helpful for us to deciede the approach.

Use a user in database definition that has the minimum privileges.

Regards
Michel
Re: Risks or disadvantages of using database links [message #416882 is a reply to message #416629] Wed, 05 August 2009 00:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
reality is if you want database to database access to the data, you will be using dblinks.

I use dbinks on from time to time. Never had any big problems with them. last two problems I did have are 1) certain code features do not work over a database link (look at the documentation or contact metalink for a list), 2) on rare ocassions, bugs can manifest with complex queries, especially if the databases are different versions.

I say go ahead and use the links.

Kevin
Re: Risks or disadvantages of using database links [message #416902 is a reply to message #416614] Wed, 05 August 2009 01:33 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
DB Links does not work when the linked database is down,thus making your application another database dependent.
Also user/pass of the database changes you have to change it in the link.
Can be a low performance
you can use advanced queues to send data from one db to another.
Re: Risks or disadvantages of using database links [message #416904 is a reply to message #416902] Wed, 05 August 2009 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The original poster states that he needs information from two different databases. AQ is not really an option then, since it is a-synchronous. Besides, if one of the databases is down, you have a problem that needs fixed, not worked around.
And as for low performance: show me a single AQ solution that out-performs a dblink please..
Re: Risks or disadvantages of using database links [message #416907 is a reply to message #416904] Wed, 05 August 2009 01:46 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I am not saying AQ is better than DBlinks but if you want to make your system independent of all other systems.Then AQ is a better option(in fact it is a deffered process)(as in my opinion no system in this world can 365 days).
I have just little exp in Oracle and seeing your posts I will ask the poster to follow what you have said.
Smile

Regards
Re: Risks or disadvantages of using database links [message #416921 is a reply to message #416614] Wed, 05 August 2009 03:17 Go to previous messageGo to next message
akool
Messages: 2
Registered: August 2009
Junior Member
Thanks to everybody for their help.Based on your suggestions,it looks like we can finalize the option of using DB links in a materialized view, without any major issues. We have considered the issues related to password change, and this can be kept at low priority, as this activity is done very rarely.
Also, our application needs both the databases to be up and running (even if we do not use the DB links).

Many thanks for your advice.
Cheers!!


Regards,
AK
Re: Risks or disadvantages of using database links [message #416945 is a reply to message #416921] Wed, 05 August 2009 05:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you want to create an MV based on a join over two databases, I suggest you create an MV that replicates t1@a onto t1@b, then create another MV that joins t1@b and t2@b.

This will avoid nasty side-affects of joining across a DB link. Both MVs should be able to be set up as FAST refresh.

Ross Leishman
Re: Risks or disadvantages of using database links [message #416989 is a reply to message #416945] Wed, 05 August 2009 09:29 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, I must have missed that in my first reading of the OP's post. It is one thing to query data from another DB over alink, quite another issue to create materialized views using data from multiple databases. I too would create a local copy of the data and then build my MVIEW from all local copies.

Would be interesting though to do some real research to see how MVIEW that used multiple databases worked, if at all.

Kevin
Previous Topic: EXISTS in UPDATE statement
Next Topic: fast operation
Goto Forum:
  


Current Time: Mon Dec 05 04:42:18 CST 2016

Total time taken to generate the page: 0.13315 seconds