The Background
I have an Oracle 11g database. Or at least, it’s not mine – I’m not a DBA – but I have query-only access to view the data.
And I need it in a MS SQL Server database. I’ve got SQL Server 2014 Developer Edition.
There are a bunch of ways to copy tables and/or data from one to the other, but the one that seemed most sensible was Microsoft’s SQL Server Migration Assistant for Oracle (or SSMA, to its friends).
I needed to do some tweaking to connect to my local MS SQL database (from memory, I needed to start the SQL Server Browser service in the SQL Server Services section of the SQL Server Configuration Manager Utility). But that was (comparatively) simple.
The Problem
The real fun started when I tried to connect to the Oracle database. Which I have regular user access to. I can see all the tables, but I’m not a DBA.
And some very clever person discovered that SSMA needs to read from the SYS.MLOG$ table, which regular users can’t see. Certainly I can’t. The suggestion was to simply
grant select on sys.mlog$ to MyUser
…which would probably work, but I don’t have permission to grant access to anyone.
The Workaround – Part 1
So what to do? The SSMA tool wants to see the SYS.MLOG$ table, and I can’t see it, nor can I give myself permission to see it. One thing I could do, though, was create a different table, with the same fields, and hopefully re-direct SSMA to look at that. Sounds easy, no?
The first step was fairly simple. I created a new table in the Oracle database called MYNEWMLOG (a nine-character title – you’ll see why in a moment). It has to have the same fields as the real SYS.MLOG$ table, which I found on this page but have listed here for posterity:
FLAG - Integer LOG - String MASTER - String MOWNER - String MTIME - Date OLDEST - Date OLDEST_NE - Date OLDEST_OI - Date OLDEST_PK - Date OLDEST_SE - Date OSCN - Integer TEMP_LOG - String TRIG - Integer YOUNGEST - Date YSCN - String
So now we have a table that we can access, that is a clone of SYS.MLOG$ (though without any data), that SSMA can look at. All we need to do now is tell SSMA to look at it.
The Workaround – Part 2
OK. So this is the bit that’s a bit … well, dodgy. I searched for the SYS.MLOG$ query that David Baxter Browne mentioned, and eventually found it in Program Files (x86)\Microsoft SQL Server Migration Assistant for Oracle\bin\Microsoft.SSMA.Framework.Oracle.Generic.dll.
Yep. The query is embedded in a DLL.
So of course that’s the end of the road, for anyone who wants to remain on the right side of the license agreement. In theory, you could (I expect) download a hex editor and edit the DLL file, and replace the nine-character Unicode string “SYS.MLOG$” with the nine-character Unicode string “MYNEWMLOG” wherever it appears. In theory. The strings are the same length so it wouldn’t change the length of the DLL, which is probably a good thing.
And provided you did that carefully, you could (I imagine) probably use the SSMA tool to copy the schema from your old Oracle database to your new MS SQL database.
In theory. I expect. But if course I wouldn’t know for sure.
Brendan Codes 