Home » RDBMS Server » Server Utilities » importing in 9i
importing in 9i [message #240310] Thu, 24 May 2007 10:33 Go to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
I currently have an export file that contains schemas in which I would like to dump into a database where the schemas are non-existent. SHould I create the users and grant them connect privileges and then do the import or does the import take care of the creation of the schema along with all the grants? Someone help me Embarassed
Re: importing in 9i [message #240318 is a reply to message #240310] Thu, 24 May 2007 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this a full export, there are the user creation statement inside.
If this an export of a list of users, you have to precreate them with necessary quota on tablespaces.

Regards
Michel
Re: importing in 9i [message #240324 is a reply to message #240318] Thu, 24 May 2007 10:56 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
create the user in database then use this clause

fromuser....touser
Re: importing in 9i [message #240340 is a reply to message #240324] Thu, 24 May 2007 11:37 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
Thank you for both your responses

Here the import syntax I would like to utilize

imp file=/home/orace/backup_refresh.dmp log=/home/oracle/backup_refresh.log
fromuser=('EAS_SEC','EASBOBJ') TOUSER=('EAS_SEC','EASBOBJ')
FULL=Y IGNORE=Y

Does this seems correct? Again, thanks so much for your help!
Re: importing in 9i [message #240344 is a reply to message #240340] Thu, 24 May 2007 11:47 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
I am not sure about multipe users in FROMUSER TOUSER clause but you can do one by one.

[Updated on: Thu, 24 May 2007 11:48]

Report message to a moderator

Re: importing in 9i [message #240386 is a reply to message #240310] Thu, 24 May 2007 13:34 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
I am getting a new error. Please look at it below:

[oracle@fulla ~]$ imp file=/backup_refresh/easiv_easp1_05212007.dmp log=/backup_refresh/easp_dump_eas4bobj1.log ignore=y touser=EAS4BOBJ fromuser=EAS4BOBJ buffer=100000

Import: Release 10.2.0.1.0 - Production on Thu May 24 08:29:25 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: sys
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by EASIV, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00009: abnormal end of export file
IMP-00034: Warning: FromUser "EAS4BOBJ" not found in export file
Import terminated successfully with warnings.

CAN SOMEONE TELL ME WHAT I'M DOING WRONG?
Re: importing in 9i [message #240388 is a reply to message #240386] Thu, 24 May 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
FromUser "EAS4BOBJ" not found in export file

You're trying to import a user that does not exists in the dump file.

Regards
Michel
Re: importing in 9i [message #240389 is a reply to message #240310] Thu, 24 May 2007 13:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>CAN SOMEONE TELL ME WHAT I'M DOING WRONG?
FromUser "EAS4BOBJ" not found in export file
What part of the statement above do you NOT understand?

Michel,
you are/were a bit faster than me on this 1.

[Updated on: Thu, 24 May 2007 13:41] by Moderator

Report message to a moderator

Re: importing in 9i [message #240395 is a reply to message #240389] Thu, 24 May 2007 14:34 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
Michael -
A full export was performed. So after I posted my response I tried doing a full import and then got

IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (SYS.PRCSJOB_ALRATIOS) violated - parent key not found
Column 1 12903820
Column 2 11415
Column 3 2007
Column 4 0006
Column 5 0006
Column 6 EABA

..I then tried to do a full import without constraints and the same thing happened? Am I still doing something wrong?
Re: importing in 9i [message #240396 is a reply to message #240310] Thu, 24 May 2007 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post results from following command:
grep EAS4BOBJ /backup_refresh/easiv_easp1_05212007.dmp | wc -l
Re: importing in 9i [message #240399 is a reply to message #240310] Thu, 24 May 2007 14:52 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
I have the command but still waiting. The dumpfile is 30G in size!
Re: importing in 9i [message #240400 is a reply to message #240395] Thu, 24 May 2007 14:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh! client object in SYS schema!
Export/import do not manage SYS objects.
It's over for you.
You have to manually handle each error one after the other trying to reconstruct something correct.
Better try to fix original database getting all non SYS objects out of SYS schema and re-export.

Regards
Michel
Re: importing in 9i [message #240401 is a reply to message #240310] Thu, 24 May 2007 14:57 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
the command returned with no output. Like I said here is my comma for the import:

imp file=/backup_refresh/easiv_easp1_05212007.dmp log=/backup_refresh/easp_dump_eas4bobj1.log ignore=y buffer=100000 CONSTRAINTS=N FULL=Y
It started working to a point then it started with the IMP-0019 and ora-02291 errors. Guys, thanks for your help I really do appreciate everything...
Re: importing in 9i [message #240403 is a reply to message #240310] Thu, 24 May 2007 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>the command returned with no output.
This confirms that as far as IMPORT is concerned this user does NOT exist within the dump file.
The 1st thing a person should do when they find themselves in a hole is to stop DIGGING.
Follow Michel's advice.
Re: importing in 9i [message #240404 is a reply to message #240310] Thu, 24 May 2007 15:04 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
So Michael -
The problem is with the db where the exported is coming from? or with my target db?
Re: importing in 9i [message #240407 is a reply to message #240404] Thu, 24 May 2007 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, the source db.

Regards
Michel
Re: importing in 9i [message #240420 is a reply to message #240407] Thu, 24 May 2007 15:47 Go to previous messageGo to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
Thanks for the input Michel. I forward to the appropriate party. Oh but were you mean ("What part of ...don't you understand?"
Re: importing in 9i [message #240853 is a reply to message #240420] Sat, 26 May 2007 08:59 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It was @anacedent who said that, not Michel:
anacedent
>CAN SOMEONE TELL ME WHAT I'M DOING WRONG?
FromUser "EAS4BOBJ" not found in export file
What part of the statement above do you NOT understand?


It means that the error message was
Quote:
FromUser "EAS4BOBJ" not found in export file
which says that there's no user called "EAS4BOBJ" in export file. So, how can you expect it to work if there's no such user?

I hope you understand it now.
Previous Topic: Export fail - oracle 8i
Next Topic: Export from Oracle 9.2.0.3 : EXP-00056: ORACLE error 4068 encountered
Goto Forum:
  


Current Time: Thu Dec 08 14:35:50 CST 2016

Total time taken to generate the page: 0.11731 seconds