Oracle DB link to mysql ... query from table

Posted on 2012-09-10
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
    LVL 73

    Expert Comment

    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 73

    Expert Comment

    view and materialized view syntax in attachment

    Author Comment

    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 73

    Accepted Solution

    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

    Alright thank you for your input!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access Required to Import Oracle Database Table 3 34
    Oracle Pivot 2 23
    Database Design Concept 3 26
    MySQL Error 3 11
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now