SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to import Oracle dmp file into SQL Server 2000 or 2005?


How to import Oracle dmp file into SQL Server 2000 or 2005?

Author
Message
GivThx
GivThx
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 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





Greg Charles
Greg Charles
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11069 Visits: 5985

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
GivThx
GivThx
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 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





ricfors
ricfors
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 377
Try searching the web for "oracle import" or similar.
A good start for import/export is http://orafaq.com/faqiexp.htm

/ricardo
Sugesh Kumar
Sugesh Kumar
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8419 Visits: 358
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
Bob Fazio
Bob Fazio
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2906 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.


Bill Wagman
Bill Wagman
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 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.



GivThx
GivThx
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 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





Bill Wagman
Bill Wagman
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 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.



Ayyappan Thangaraj
Ayyappan Thangaraj
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search