2

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...

  • Now I read your question again and I am not sure if I understand it right. You want to avoid to import it in an Oracle database and so avoid to use you replication tool. Is this right? – miracle173 Jan 2 '18 at 8:03
  • We get dump files from another company and they use Oracle. We don't use, we had to install Oracle for that. We want to use data in SQL Server. If it is not possible to move data, We have to use Oracle DB – AhmetMelihBaşbuğ Jan 2 '18 at 8:22
  • I updated my answer. Please read note 3. Maybe it make sense to open a new question and provide more details. – miracle173 Jan 2 '18 at 10:18
4

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.

  • there are 5-6 schemas which have 7-8 tables. some tables have 1 billion rows. There are no constraits. Data includes string and number. My collagues don't use Oracle. They used to use SQL Server. So they want to move them – AhmetMelihBaşbuğ Jan 3 '18 at 8:26
0

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.

0

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.

  • Is that tool available for free? – dezso Sep 10 '18 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.