Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to import Oracle dmp file into SQL Server 2000 or 2005? Expand / Collapse
Author
Message
Posted Wednesday, July 18, 2007 11:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 11:50 AM
Points: 84, Visits: 104

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




Post #382925
Posted Wednesday, July 18, 2007 2:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:49 PM
Points: 4,065, Visits: 5,277

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
Post #383001
Posted Wednesday, July 18, 2007 4:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 11:50 AM
Points: 84, Visits: 104

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




Post #383039
Posted Thursday, July 19, 2007 12:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 4:28 AM
Points: 20, Visits: 303
Try searching the web for "oracle import" or similar.
A good start for import/export is http://orafaq.com/faqiexp.htm

/ricardo
Post #383102
Posted Thursday, July 19, 2007 2:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 28, 2013 8:25 AM
Points: 3,461, Visits: 347
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
Post #383114
Posted Thursday, July 19, 2007 8:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, Visits: 683

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.

Post #383294
Posted Thursday, July 19, 2007 3:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 11:31 AM
Points: 119, Visits: 13
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.


Post #383474
Posted Thursday, July 19, 2007 3:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 11:50 AM
Points: 84, Visits: 104

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




Post #383478
Posted Thursday, July 19, 2007 4:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 3, 2009 11:31 AM
Points: 119, Visits: 13
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.


Post #383482
Posted Wednesday, February 20, 2013 10:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:46 PM
Points: 40, Visits: 154
We can use Third party tool to export data from Oracle dump file to SQL Server.

http://www.convert-in.com/dump-read-api.htm
Post #1422175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse