Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Oracle DB link to mysql ... query from table

Posted on 2012-09-10
Medium Priority
Last Modified: 2012-09-10

let me first explain my situation. I am running Oracle database 10.2 on linux and i recently made an database link to mysql database. On that Mysql database i got access to one table which is daily populated with data.

Now i want to know what are my options of creating an identical table on my (Oracle) database and populate it with records from table on MySql?  When insert happens on MySql table it should also happen on my Oracle table.

I hope i explained it well enough.

Thank you for your input!
Question by:Caruso_eu
  • 3
  • 2
LVL 74

Expert Comment

ID: 38382931
if you always want the data in Oracle to reflect what is in MySQL, then you might want to create a view instead.

if querying across the link in real time to support the view isn't feasible, then create a materialized view with whatever refresh frequency is adequate.
LVL 74

Expert Comment

ID: 38382944
view and materialized view syntax in attachment

Author Comment

ID: 38383389
Thank you for providing me with examples!

Got one more question ... performance wise. What if i set query to 1 minute intervals ... how much load on the database are we talking here?

Ps We are not talking about 1 million records in table here. ... will be far less.
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 38383494
you'll have to test it.  The load will be determined by the type and size of data, network traffic, other activity on either or both systems.

Refreshing every minute could be pretty intense though if you have thousands of rows with lots of large columns.

Author Comment

ID: 38383778
Alright thank you for your input!

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question