0

I have a client with close to 120,000,000 records in an Oracle database. Their engineer claims they can only give us a ms access dump of their database. The data will actually be going into an MySQL relational database instance.

What potential issues and problems can we expect moving from Oracle > Access > MySQL?

We have located tools that can convert oracle db to MySQL, but due the large nature of the database 100gb + I am not sure of the stability of these software based solutions to handle the conversion process. This is a time sensitive project and I am worried that if we make any mistakes in the onset that we may not be able to complete in a timely manner.

Charu Khurana
  • 4,511
  • 8
  • 47
  • 81
jeremiah
  • 3
  • 1
  • 1
    access has comparatively VERY limited field types v.s. oracle. unless the db has just basic int/char/varchar type stuff, you can probably expect some field mangling. – Marc B May 30 '13 at 16:27
  • 1
    Access? With such a huge database? I'd be scared too... – eternay May 30 '13 at 16:27
  • That's what I was thinking as well marc and eternay – jeremiah May 30 '13 at 16:29
  • I'd see about having them dump a file of `INSERT` commands in MySQL syntax. You could probably cook up a query to do that. I'd also do the MySQL equivalent of `ALTER TABLE tbl_name NOLOGGING` before running the `INSERT` script. According to [this StackOverflow question](http://stackoverflow.com/a/2958865/2091410) it's done with the `sql_log_bin` session variable. – Ed Gibbs May 30 '13 at 16:48
  • 2
    100GB in an Access database? – ypercubeᵀᴹ May 30 '13 at 17:08

2 Answers2

8

Exporting the Oracle data to a comma-separated, tab separated, or pipe separated set of files would not be very challenging. It's done all the time.

I have no idea why someone would claim to only be able to produce an MS Access dump from an Oracle database -- if that's not being done directly via selecting from Access through ODBC then it's done via an intermediate flat file anyway. I'm inclined to call "BS" or "incompetence" on this claim.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • +1 this is your answer OP. Move the data to a flat file and then import that file. Ignore the access garbage. – Zane May 30 '13 at 17:11
3

The maximum size of an Access database is 2GB so I don't see how the proposed migration could be achieved without partitioning the data.

Daniel Renshaw
  • 33,729
  • 8
  • 75
  • 94