We need to move some tables and schemas from an Oracle database to an MS SQL Server database. We have only Oracle dump files. We import them into an Oracle database. Then we need to use a replication tool to move the data from Oracle to the SQL Server. Is it possible to bypass the replication tool for the moving task? Is it possible to move data from Oracle to the SQL Server using Oracle RMAN or DUMP files? Thanks...
No, I don't think that this is possible. Both tools use Oracle specific file formats and can work only with Oracle databases. So the way to move data from an Oracle dump file created by the Oracle exp or expdp tool is to import the dump in an Oracle database and move it from this database to an SQL Server database.
Note 1: Toad from Quest is able to read Oracle export dumps and I cannot be 100% sure that there exists a tool that hacked the Oracle dump format to read the data and write it to SQL server.
Note 2: It is possible to migrate data from an Oracle database to a Microsoft SQL Server database. Evidently Oracle ist not much interested in such migrations but Microsoft is, so you should search the Microsoft pages for tools, e.g. here is one for Oracle 12c and SQL Server 2017. HR is a sample schema in provided by Oracle. Oracle 9i is a rather old version, so I am not sure if this will work for Oracle 9i.
Note 3: I think it will make sense that you describe the whole problem more detailed. Why do you want to copy the data to your system, what are the requirements and constraints. What is the size of the data, what type of objects do you want to migrate,... Maybe it make sense to open a new question that describes the whole scenario. Maybe the question that you put here doesn't really help you to solve your original problem.
I don't think you can restore directly in SQL from a Oracle dump either but if you restore it in Oracle, you can create a SSIS package that will use a connector to Oracle to read the data from Oracle and write it in SQL.
I hope it will help.
You can use Oracle Golden gate. It replicates metadata from oracle to any non oracle database and vice versa. It may not be helpful if data is available in only dump files.