July 18, 2007 at 11:47 am
Hi, Gurus
Q for you.. I have a .dmp file that has been created by Oracle. I want to import this file into SQL server 2000 or 2005 without restoring into Oracle db which I dont know how to do that.
Any idea?
Thx in advance
Jay
July 18, 2007 at 2:22 pm
Sorry, it can't be done. Oracle dmp files can only be read by the Oracle import utility. You'll have to import the contents of the file into an Oracle database then import the data into SQL Server using DTS or SSIS.
Greg
Greg
July 18, 2007 at 4:10 pm
OK.. Then
I dont have much choices.. I try learn , I mean quick learn, how to import dmp file..
I am looking at article about importing but do you, I mean anybody, have any web site that I can reference?
Thx
Jay
July 19, 2007 at 12:56 am
Try searching the web for "oracle import" or similar.
A good start for import/export is http://orafaq.com/faqiexp.htm
/ricardo
July 19, 2007 at 2:18 am
You can use the import/export option to import to sql server the data can be imported.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 19, 2007 at 8:22 am
Can you elaborate on that option? Or did you miss-read the post. Even Oracle has issues between versions of .dmp files, and they created them.
Using the imp/exp commands from Oracle, you can import into an Oracle DB. Then you can use the import/export from Enterprise Manager/Management Studio to import from Oracle into SQL Server.
Can't say for sure, but you might be able to do something with DTS, but I have never tried.
My VS 2005 is screwed up at the moment, or I would look at integration packages.
July 19, 2007 at 3:34 pm
DOcumentation for the import/export utility can be found at http://www.oracle.com/technology/index.html. Look under the version of oracle you are using for the utilities manual. It documents import/export. I am assuming you have access to a machine with an oracle database on it already. If you want the whole database you should create an empty database and do a full import. If you only want a particular user precreate that user in the Oracle database and qualify the import command with fromuser=<user in the original database> touser=<user in the database you are importing into>. That shoould do it.
July 19, 2007 at 3:50 pm
Thx.. Bill
Actually that is what I am trying to do but I can not access db. I've got an error message saying that Protocol Adapter error and other one is login failure.. I think there is internal password to access master db (I assume that is master db)..
Yes.. I used FROMUSER but did not work so that I am using FULL which is full import but no luck so far..
Jay
July 19, 2007 at 4:09 pm
Jay, without more information I am not really able to assist further. The first thing I would suggest you do is find the DBA for the Oracle database, you are most likely going to need some assistance with this. You need to be able to get connected to the database. Do you have a login to the box on which the Oracle database resides? If not you will have to connect remotely, possible. In either case the DBA is going to have to assist you. If you are on your own then you may have a long haul trying to learn enough about Oracle to configure the connectivity. Once you get connected the user you connect to must have privileges to import, again you may need assistance from the DBA. Once you get to that point the import is fairly straightforward. If you are unable to resolve the connectivity issues then it may be easier to just connect directy to the oracle database from which the export file you have came form and move data directly from there. Good luck.
February 20, 2013 at 10:05 am
We can use Third party tool to export data from Oracle dump file to SQL Server.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply