Home » RDBMS Server » Server Utilities » Error during Import (Oracle 10g)
Error during Import [message #338170] Mon, 04 August 2008 01:28 Go to next message
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

Re: Error during Import [message #338173 is a reply to message #338170] Mon, 04 August 2008 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: Error during Import [message #338176 is a reply to message #338173] Mon, 04 August 2008 01:53 Go to previous messageGo to next message
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.
Re: Error during Import [message #338184 is a reply to message #338176] Mon, 04 August 2008 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
Re: Error during Import [message #338187 is a reply to message #338185] Mon, 04 August 2008 02:46 Go to previous messageGo to next message
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
Re: Error during Import [message #338189 is a reply to message #338170] Mon, 04 August 2008 02:54 Go to previous messageGo to next message
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
Re: Error during Import [message #338190 is a reply to message #338187] Mon, 04 August 2008 03:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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?
Re: Error during Import [message #338191 is a reply to message #338189] Mon, 04 August 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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
Re: Error during Import [message #338192 is a reply to message #338190] Mon, 04 August 2008 03:03 Go to previous messageGo to next message
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?
Re: Error during Import [message #338193 is a reply to message #338192] Mon, 04 August 2008 03:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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?
Re: Error during Import [message #338194 is a reply to message #338193] Mon, 04 August 2008 03:12 Go to previous messageGo to next message
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.
Re: Error during Import [message #338200 is a reply to message #338194] Mon, 04 August 2008 03:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 ./fa/3314/0/. What does this: "commit=y feedback=1000 ignore=y" do? I mean, why did you include these options?
Re: Error during Import [message #338208 is a reply to message #338200] Mon, 04 August 2008 03:44 Go to previous messageGo to next message
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



Re: Error during Import [message #338213 is a reply to message #338191] Mon, 04 August 2008 03:49 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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


Re: Error during Import [message #338228 is a reply to message #338213] Mon, 04 August 2008 04:39 Go to previous messageGo to next message
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
Re: Error during Import [message #338232 is a reply to message #338228] Mon, 04 August 2008 04:52 Go to previous messageGo to next message
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.
Re: Error during Import [message #338234 is a reply to message #338232] Mon, 04 August 2008 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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
Re: Error during Import [message #338237 is a reply to message #338234] Mon, 04 August 2008 05:15 Go to previous messageGo to next message
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........
Re: Error during Import [message #338240 is a reply to message #338237] Mon, 04 August 2008 05:21 Go to previous message
Littlefoot
Messages: 20901
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.
Previous Topic: How to load cognos dump into Oracle
Next Topic: sql *loader
Goto Forum:
  


Current Time: Sat Dec 10 01:03:34 CST 2016

Total time taken to generate the page: 0.09214 seconds