OraFAQ Forum: Server Utilities » Error during Import

Home » RDBMS Server » Server Utilities » Error during Import (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error during Import [message #338170]

Mon, 04 August 2008 01:28

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi,
I have received a dump file from client...not sure which version is it.
I am trying to import database through TOAD but this is giving the below error.
[CODEimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)

IMP-00025: for DBA export files, FROMUSER must be specified with TOUSER option][/CODE]

I am importing for the first time.

Please advice as what could be the possible reasons and how to import it successfully.

Thanks in advance,
Mahi

Report message to a moderator

Re: Error during Import [message #338173 is a reply to message #338170]

Mon, 04 August 2008 01:36

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is quite clear:

Quote:

for DBA export files, FROMUSER must be specified with TOUSER option

This is the Utilities book; find chapter you're interested in (is it "Original Export and Import" (chapter 19)?), study it and get familiar with options described in error message.

Then try to perform import again and - possibly - return here to explain what you did and what happened.

Report message to a moderator

Re: Error during Import [message #338176 is a reply to message #338173]

Mon, 04 August 2008 01:53

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi Littlefoot,
Isn't there any problem due to the first two lines regarding CharacterSet.

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)

I run the below query

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
AL32UTF8

Does that mean I need to change the NLS parameter as :

c:\set NLS_LANG =
AMERICAN_AMERICA.WE8MSWIN1252

before import.

Please advice .... I am looking into the link you have given ...meanwhile please let me know if it helps this way.

Report message to a moderator

Re: Error during Import [message #338184 is a reply to message #338176]

Mon, 04 August 2008 02:33

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nothing to worry but your NLS_LANG should be either the one of export file or the one of database (WE8MSWIN1252 or AL32UTF8).
There is one point to take care: character conversion may lead to take more bytes on UTF8 and so some columns may be to small for your values.

Regards
Michel

[Updated on: Mon, 04 August 2008 02:35]

Report message to a moderator

Re: Error during Import [message #338185 is a reply to message #338176]

Mon, 04 August 2008 02:34

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As far as I can tell, character set message said that there's "possible charset conversion". Import failed because you missed FROMUSER and TOUSER options. Once you decide to include them into the IMP command, you'll see what happens.

It should look like this:

C:\>imp system/pwd@ora10 file=emp.dmp tables=test fromuser=scott touser=mike

Report message to a moderator

Re: Error during Import [message #338187 is a reply to message #338185]

Mon, 04 August 2008 02:46

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi Littlefoot,
As I said this is first time I am importing data....I was trying to import through Toad and there is no option for From user.
Also I have only a .dmp file.
I know the login and password and the database name
that is the part : system/pwd@ora10 file=emp.dmp

How to get the rest values when I am only having a dump file.
Please bear with me... as I am asking very basic questions:

C:\>imp system/pwd@ora10 file=emp.dmp tables=test fromuser=scott touser=mike

Report message to a moderator

Re: Error during Import [message #338189 is a reply to message #338170]

Mon, 04 August 2008 02:54

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi,
I decided to run the import from command prompt and not from Toad.
I got an hint to do import as below:

C:\ imp user/password@mydb c:\empdata.dmp c:\implogfile.log fromuser=xxx touser=xxx commit=y feedback=1000 ignore=y

I have only a dmp file.

I know the first part : C:\ imp user/password@mydb c:\empdata.dmp c:\implogfile.log

How to get the value for fromuser=xxx touser=xxx

Should I put in place of touser as the user and what to put for fromuser.

Please advice

Report message to a moderator

Re: Error during Import [message #338190 is a reply to message #338187]

Mon, 04 August 2008 03:00

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

I was trying to import through Toad

That was a bad idea; I believe that you should get familiar with these (Export, Import, SQL*Loader, ...) utilities, learn how to use them in their "natural" environment - and that's operating system command prompt. TOAD and other GUIs may be helpful, but only if you know WHAT you are doing and WHY. Clicking here and there is not exactly the best way to accomplish any task.

It appears that the only unknown piece of information is FROMUSER. I guess you know your (target) database's privileged username (which should not be SYSTEM nor SYS), its password and database alias. Also, it is self-understandable that you know TOUSER - a target schema. As you got the DMP file from your client, ask him/her which schema did (s)he export - then you'll know FROMUSER too.

Or, you might perform full import which would then import, hm, all IMP can find in the DMP file, including this "fromuser" schema. The question is - do you really want to do that?

Report message to a moderator

Re: Error during Import [message #338191 is a reply to message #338189]

Mon, 04 August 2008 03:01

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You should REALLY read the link Littlefoot posted you instead of trying to find the correct syntax by trials and errors (and wasting our time).

Regards
Michel

Report message to a moderator

Re: Error during Import [message #338192 is a reply to message #338190]

Mon, 04 August 2008 03:03

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

ya its a full database dump....
does that mean that from user and touser would be same here?

Report message to a moderator

Re: Error during Import [message #338193 is a reply to message #338192]

Mon, 04 August 2008 03:05

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If it is FULL import, then you don't need FROMUSER and TOUSER. However,

LF's previous message

The question is - do you really want to do that?

Report message to a moderator

Re: Error during Import [message #338194 is a reply to message #338193]

Mon, 04 August 2008 03:12

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

it means the command should be like :

C:\ imp user/password@mydb c:\empdata.dmp c:\implogfile.log commit=y feedback=1000 ignore=y

Its a full dump for testing the main database.

Report message to a moderator

Re: Error during Import [message #338200 is a reply to message #338194]

Mon, 04 August 2008 03:28

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

virmahi

I got an hint ...

It sounds as if there IS someone who can help you do the import. Why don't you ask him/her to help you?

virmahi

it means the command should be like :

If you say so; I don't know - maybe, maybe not /forum/fa/3314/0/

/forum/fa/3314/0/

. What does this: "commit=y feedback=1000 ignore=y" do? I mean, why did you include these options?

Report message to a moderator

Re: Error during Import [message #338208 is a reply to message #338200]

Mon, 04 August 2008 03:44

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

got the hint from google....

C:\ imp user/password@mydb c:\empdata.dmp c:\implogfile.log commit=y feedback=1000 ignore=y

Should I run the import using :
C:\ imp user/password@mydb c:\empdata.dmp c:\implogfile.log

Report message to a moderator

Re: Error during Import [message #338213 is a reply to message #338191]

Mon, 04 August 2008 03:49

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 04 August 2008 10:01

You should REALLY read the link Littlefoot posted you instead of trying to find the correct syntax by trials and errors (and wasting our time).

Regards
Michel

Report message to a moderator

Re: Error during Import [message #338228 is a reply to message #338213]

Mon, 04 August 2008 04:39

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Thanks Littlefoot,
I was able to import tables to the database. I was getting error as unable to extend to tablespace and the import rollbacked.
It was due to space limitation then I imported for only required tables giving your syntax <tables = tablename> and it worked.

I am looking into the link given by you for the solution for space crunch as what to do when I get such error.

Thanks for all your help and bearing with me,
Mahi

Report message to a moderator

Re: Error during Import [message #338232 is a reply to message #338228]

Mon, 04 August 2008 04:52

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

I am so sorry to bother you again. I was able to import two small tables but stuck while importing a bigger table.
I got the below message:

. . importing table                       "RESULT"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table LIMS.RESULT by 1024 in tablespace LIMS
IMP-00028: partial import of previous table rolled back: 4087430 rows rolled bac
k
Import terminated successfully with warnings.

Report message to a moderator

Re: Error during Import [message #338234 is a reply to message #338232]

Mon, 04 August 2008 05:06

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-01653: unable to extend table %s.%s by %s in tablespace %s
 *Cause:  Failed to allocate an extent of the required number of blocks for
          a table segment in the tablespace indicated.
 *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
          files to the tablespace indicated.

Regards
Michel

Report message to a moderator

Re: Error during Import [message #338237 is a reply to message #338234]

Mon, 04 August 2008 05:15

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Hi Michel,
Before I got your answer I increased the size of tablespace to 2 GB which was set at 1 GB earlier. My dump file is 2 GB. Its in import process currently........

What do you suggest...should I add a datafiles under that tablespace instead and give some name to that datafile........

Report message to a moderator

Re: Error during Import [message #338240 is a reply to message #338237]

Mon, 04 August 2008 05:21

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It seems that you are looking for shortcuts. Being a DBA is not a game. It also takes significant time to study things, along with certain experience.

What did you mean by

Quote:

should I add a datafiles under that tablespace instead

If not under "that" tablespace (that lacks in free space), which other tablespace do you have on mind?

Quote:

... and give some name to that datafile

By all means! Unless you can create a file without a name.

Report message to a moderator

Previous Topic:	How to load cognos dump into Oracle
Next Topic:	sql *loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 11 08:58:36 CDT 2024