1

I have 2 databases, the first one is a Mysql db and it is used for a website. The second one is an Oracle db and it has data that I want to show on the website and that data must be fresh, I mean, I need to execute a process to migrate data to Mysql from oracle db every 30 minutes.

Because I am talking about 60000 rows to migrate every 30 minutes, I think that optimal way to do it is something like (thinking about performance):

insert into mysql_db.table.field1, mysql_db.table.field2, mysql_db.table.field3 select oracle_db.table.field1, oracle_db.table.field2, oracle_db.table.field3 from oracle_db.table

The Oracle db is on Windows and Mysql is on Linux (Ubuntu).

Is that possible? how? else, suggest me a different way please.

NullUserException
  • 83,810
  • 28
  • 209
  • 234
el_quick
  • 4,656
  • 11
  • 45
  • 53
  • 1
    May we take it there is something stopping you from getting your data directly from Oracle via PHP? – Hugh Jones Oct 19 '11 at 15:10
  • What's wrong with querying the Oracle DB directly? Better yet, why are you using 2 databases? – NullUserException Oct 19 '11 at 15:31
  • Well, the oracle db contains sensitive data, the website is new and was wrote on RoR framework, the idea is don't touch the website code. and don't connect directly from website to oracle db (security issues cause I don't know how oracle db is administrated) – el_quick Oct 19 '11 at 15:41

3 Answers3

1

Having ODBC driver for the MySQL database, you could try Data Export tool (with command line support) in dbForge Studio for Oracle.

Devart
  • 119,203
  • 23
  • 166
  • 186
0

You could also use something like GoldenGate to capture changes in oracle and apply them to the mysql database , although if this is the one use case you have, the cost of the product may not be justifiable, in which case probably just a simple perl/php/python/etc script would do the trick.

I've used HSODBC links between mysql 4 and oracle 9i and found the performance less than fantastic, hopefully things have improved, if that is the case, this may be a viable solution, however you would still need to do it as a job ( inside or out of oracle ), as I'm not aware of anyway to make calls from mysql to oracle.

Matthew Watson
  • 14,083
  • 9
  • 62
  • 82
-1

Oracle support the connection between Oracle databases through DBLink. But I don't believe that exists some tool for connect a Mysql and Oracle that let you execute directly your proposed query.

I can suggest that you write an script (by example in Python or Groovy) and schedule it on CRON Linux (if this is your environment). Since the size of data you will need implement Batch Updates (it's specific to the language in which you will implement the script)

Ernesto Campohermoso
  • 7,213
  • 1
  • 40
  • 51