Migrating Oracle Views to SQL Server

Question

In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table objects was nearly 30,000 according to the dba_tables listing. Use of the Import/Export wizard was immediately discounted and we don't have knowledge of BIML to build a SSIS-centric solution. We tried using Microsoft SQL Server Migration Assistant 7.10 for Oracle but the utility was non-responsive (probably stuck trying enumerate all the objects).

We did a crude workaround constructing SELECT * INTO <SQLTable> FROM OPENQUERY("SELECT * FROM...<Oracle Table>") queries with a linked server (painful, but effective) and were able to import all the tables.

How to move the views over, however, is our next challenge. Can we try to use the Migration Assistant to convert the views (how do we get past it choking on the object volume)? Are there meta views in the v$ schema or elsewhere that can at least export the DDL utilized so we can then edit with the newly-imported tables?

Answer 1

Option 1

The correct answer is:

git export

In other words, the DDLs should exist in a code repository.

Option 2

Second option, use SQL*Developer's cart feature.

You can drag+drop the VIEWS of interest and export out all of the DDLs at once.

Option 3

I believe the cart feature uses DBMS_METADATA.GET_DDL() behind the scenes.

You'd have to roll your own code that loops through all of the relevant VIEWS.

Alternatives

The fastest way to do something is to not do it at all

-- Thomas Kyte

In this case: Don't migrate off of Oracle.

Most Oracle licenses are bought on a perpetual basis. You can run Oracle until the end of time, you just can't upgrade/patch it after you stop paying "maintenance and support".

I would have upgraded the database to 18c (latest) and stop paying the "maintenance and support".

"long term access" would still be a problem. I would at least investigate if 18c XE (not yet out) can accommodate all of the static data. It is supposed to include Advance Compression.

Final Thoughts

Since you said this is for READ ONLY archive purpose, I'll only remind you: "Database agnostic code is a myth".