| Problems with EXP/IMP [message #148330] |
Wed, 23 November 2005 12:08  |
ozzy80
Messages: 55
Registered: June 2005
|
Member |
|
|
Hello,
I am sure the experts might have done this before..
I have oracle 9.2.0.6, on SOlaris 8..
I was trying to create a test box and had the DBA install the DB... I wanted to test EXP/IMP myself...
so exported the dev DB with..
exp scott/tiger file=112305.dmp log=112305.log FULL=Y
exp user2/pass file=file2.dmp log=file2.log full=y
(2 diff schemas in same DB)
then tried importing this to the new DB with...
imp scott/tiger file=112305.dmp fromuser=scott log=112305_imp.log
imp user2/pass file=user2.dmp fromuser=user2 log=file2_imp.log
But the problem was, I needed another user 'User3' and the imp completed but with warnings... so I created the user3 and again ran the above imp command... I did that couple more times...
Obviously, I am pretty new to this DBA side of oracle... now I see data in some tables and few others are empty... I also read that if you run multiple IMP commands then you run into a problem of having to create duplicate constraints...
So, I was thining is there a way i can clean up this mess... or have I created a mess at all????
If yes, then plz let me know what I could do to start afresh...
Any help is greatly appreciated...
thanks,
Ozzy
|
|
|
|
|
|
| Re: Problems with EXP/IMP [message #148540 is a reply to message #148330] |
Thu, 24 November 2005 19:34  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>exp scott/tiger file=112305.dmp log=112305.log FULL=Y
>>exp user2/pass file=file2.dmp log=file2.log full=y
>>(2 diff schemas in same DB)
Unfortunately No.
When you issue FULL=y , you take the export of whole database.
>>imp scott/tiger file=112305.dmp fromuser=scott log=112305_imp.log
>>imp user2/pass file=user2.dmp fromuser=user2 log=file2_imp.log
Either this is just a typo
or
You didn't post what you did exactly.
The above syntax is wrong.
you are supposed to use fromuser=scott touser=to_the_user_you_want.
>>But the problem was, I needed another user 'User3' and the imp completed but with warnings..
What exactly? Out of around 90 errors listed here , which one are you talking about?
http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96525/impus.htm#357414
Always please post what you do.
Post your session and complete error message.
'Explaining' the error in words helps nothing.
>>I also read that if you run multiple IMP commands then you run into a problem of having to create duplicate constraints...
Actually,
by default import will append the data.
So the first import may or may not have been success.
If an object was created and If you do a consequent import (with removing the data or objects), your import session will error out stating
IMP-00015 following statement failed because the object already exists:
Cause: Import failed to create an object because it already exists.
If you specify ignore=y then this error is ignored by oracle.
So, the data will be appended.
Now, if the table has a primary key or an unique key enabled then the constraint will not allow a duplicate record ( will whine about an 'violated constraint error').
So in any case, your constraints are NOT duplicated.
Your data 'may be' duplicated, if your table has no primary/unique constraint.
>>So, I was thining is there a way i can clean up this mess... or have I created a mess at all????
Without seeing what you have actually done, I cannot guess.
Please have look into documentation.
Everything is explained and documented with examples.
http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm
and quick glance here
http://www.orafaq.com/faq/import_export
followed by a quick riffle in our forum for previous postings should help you.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
