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?