Sql-server – How to convert an Oracle dump file into SQL Server

dumporaclesql server

I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server.

I've searched for related technologies, such as Oradump to SQL Server. Do you have another suggestion to solve this? Open source ones I mean.

Thanks for both of your response.
I see how difficult it will, but is there any possibility to use another way in converting oracle dump file? because all of solution's converter tools always provide a connection database server. I'm so thankful what if you can suggest another tool. thanks anyway

Best Answer

"is there any possibility to use another way in converting oracle dump file?"

Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication)

Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse engineering it then have to start over again when the format changes. If you want to convert Oracle data to SQL, you aren't going to be able to use a dump file unless you import into Oracle first.

Related Solutions

Oracle 10g to 11g – Importing Dump Files

Your problem might very well have to do with incorrect versions or wrong default connection. Make sure that you use the exp utility from the 10g installation and the imp utility from the 11g installation. Also make sure that the environment variables like ORACLE_HOME are correct and that PATH is adjusted for the correct ORACLE_HOME/bin

How does your connect string look like? Is your TNS_ADMIN variable defined? (if not, you might need to have multiple tnsnames.ora files in place, or tnsnames.ora is in a platform specific location (/etc, /var/opt/oracle etc.)) tnsping shows where it gets the sqlnet.ora (next to tnsnames.ora) from.

Even better is to start using expdp and impdp. You can use impdp to directly import your data in the 11g database, using a datasbase link.

Windows – Import Oracle full dump file to fresh Oracle installation

If you are using the old imp method the users and tablespaces must be created first. This includes grants, database links and any advanced queues you may be using. A sample script could look like this in Windows imp a_DBA_user/apassword LOG =C:\dump\logs\import.log file =user01.dmp fromuser=user_name touser=user_name

The newer datapump method works wonderfully. You don't need to create the user but I would create any custom tablespaces you may have on the original.

A sample data pump call requires you to create a database link to the original database and a directory. This creates a directory

CREATE OR REPLACE DIRECTORY 
DATA_PUMP_DIR AS 
'C:\app\Oracle\product\11.2.0\dbhome_1\rdbms\log\';


GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO auser;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;

In your script to import set these variables NLS_LANG

ORACLE_SID= your database name

impdp a_user_who_exists/your database name directory=DATA_PUMP_DIR network_link=original database name schemas= user1,user2 LOGFILE=DataPump.log TABLE_EXISTS_ACTION=REPLACE

Best Answer

Related Solutions

Oracle 10g to 11g – Importing Dump Files

Windows – Import Oracle full dump file to fresh Oracle installation

Related Question