?
Solved

Oracle Import Alert

Posted on 2006-05-15
17
Medium Priority
?
1,802 Views
Last Modified: 2008-02-20
Hello,
I have 2 questions. I'm importing an oracle schema from an Oracle 8i database into an Oracle 9i database. The data comes thru fine but I get an alert message right before it starts importing complaining about the character set. It states the following...

Connected to: Oracle 9i Release 9.2.0.7.0 - Production
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
export server uses WE8ISO8859P1 character set (possible ncharset conversion)

1) How do I set the character set for the import utility? What am I doing wrong? I'm a bit confused here. This is the most important question.

2) Another related issue to the import is that none of the "grants" come thru on the import. I'm sure I'm doing something wrong. Any thoughts?

Thx for the help...

Ken
0
Comment
Question by:kencrest
  • 4
  • 4
  • 3
  • +3
17 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 700 total points
ID: 16687217
If you need to support non-English character data, then your character set issue is significant.  If your database though just needs to support English characters, you should be fine.

Regarding the grants, I've sometimes needed to run import twice in situations like this, the second time with "ignore=Y" and "rows=N" to make sure that the grants get imported, because otherwise some of them fail.

You are sure it is grants that are missing and not public synonyms, right?  A user-level (schema-level) export will not get public synonyms, but your application may expect them.  If that is the case, you will have to create public synonyms manually.  That is not difficult in SQL*Plus.  You can just write a query something like this to generate the statements you need, then run them with an account that is authorized to create public synonyms.

set pagesize 999;
column Command format a100;
select 'create public synonym '||object_name||' for '||user||'.'||object_name';' "Command"
from user_objects
where object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TABLE,'VIEW';

0
 
LVL 13

Assisted Solution

by:anand_2000v
anand_2000v earned 300 total points
ID: 16687947
It is just a warning....nothing to worry about. It just says that the export and import where different character sets but its possible to convert them.
0
 
LVL 6

Assisted Solution

by:makhan
makhan earned 300 total points
ID: 16688268
1.   If you want to set the parameters you can do so by

ex.

SET NLS_NCHAR=WE8ISO8859P (dos mode in a batch file)
or
NLS_NCHAR=WE8IS08859P;export NLS_NCHAR (unix/linux shell script)

2.  Capture the import log file which will show errors / warning messages by

LOG=IMPORT.LOG

option of import utility. Post the log here if u see any warnings  or error messages.

best option is to use the export utility of 9i to export and then import using import utility of 9i.

Check if the exporting user and importing user are same. Also if you are importing full database then you need to have the
IMP_FULL_DATABASE and EXP_FULL_DATABASE roles for the importing and exporting user respectively.

HTH

makhan
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Expert Comment

by:htilwankar
ID: 16688346

Oracle recommends that Character set should be same or subset of another character set when you are importing data between two DB.

You 9i has subset of 8i character set that is why oracle automatically converts its. Otherwise it won't be execute.

We can not change character set for import only, It is fixed for forever when you have created Database.

THis is all about character set but I don't know about privis ..

Regards
Hitesh
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 700 total points
ID: 16688687
Export and Import utilities do not take care about the Character Set of the database.
They use it only for conversion actions and warnings.

The only place Export and Import utilities get NLS instructions is
NLS_LANG
environment variable.

It should be set to an appropriate value - WE8ISO8859P1 in your case.
0
 

Author Comment

by:kencrest
ID: 16690662
I generally run the "imp" command for importing a user schema at the dos command prompt. Is there a way to set the character set at the dos prompt before launching the import command (i.e.  SET NLS_NCHAR=WE8ISO8859P')? This is an all English character database.

Thx
Ken
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16691308
No!
You run IMP via command line interface
BUT it reads the leading NLS instruction from NLS_LANG.
It is in the Registry, Oracle_Home, sorry for my mistake!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16691930
Schwertner is correct, import will get the NLS settings from the environment and for Oracle on Windows, that is from the registry (HKeyLocalMachine, Software, Oracle).  Oracle changed their default character set between Oracle8 and Oracle9, so that is why you see those warnings/notices, but for an English-only system, I think you can just ignore them.  If you needed to support other western European language characters, you might need something different, but I think you will be fine with the defaults for Oracle9.
0
 

Author Comment

by:kencrest
ID: 16692227
Hi markgeer/schwertner...
Thx for the additional feedback comments. I found the entry in the registry for NLS_LANG. My registry for Oracle home is set at NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252.

Sorry for being so anal, but the data is critical and I just want to make sure all my environments are exactly the same.

What would be the best way to change this in the registry? Do it manually in the registry OR use the 'Set' command in the command line interface OR set it somehow in sqlplus?

Thx for your patience...
Ken
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16692596
Just FYI, we ran our production (English-only) Oracle9i database for two years recently on a Windows server that had that same registry setting for NLS_LANG.  We had also upgraded from an Oracle8.1 database via export and import.  I don't have the import log files anymore from our 8i - 9i upgrade, but I think I remember seeing the same (or similar) NLS-related messages in our upgrade.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16696933
If it is  NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252.
The characteristics are
(SB stands for Single Bits,
 EURO stands for Euro character)

WE8MSWIN1252         MS Windows Code Page 1252 8-bit West European   SB, ASCII, EURO  West European

So you can represent all West European letters.


0
 

Author Comment

by:kencrest
ID: 16704074
...thx for the info. I actually went into the registry and set the NSL_Lang to AMERICAN_AMERICA.WE8ISO8859P1 so as to match the Oracle Database Instance.

So now I went and did a schema/user import from the old database into this new one (8i to 9i using import fromuser touser). I got a few more warnings which were...

"Warning: the objects were exported by SYS, not by you
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export server uses WE8ISO8859P1 NCHAR character set  (possible ncharset conversion)"

It's an all English character set database. I have to use the WE8ISO8859P1 character set due to 3rd party specifications for an application that runs against the database.

The warnings don't seem serious...but I just want to make sure. Am I ok? Any additional comments?

Thx for all your help and feedback...

Kind Regards,
Ken
0
 
LVL 6

Expert Comment

by:makhan
ID: 16705905
Hi,

The warnings are not serious. You should be fine now.

Check if you are getting grants to the objects which you were not getting earlier.

Regards,

makhan
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 16707322
That's exactly what I said earlier.

It is just a warning....nothing to worry about. It just says that the export and import where different character sets but its possible to convert them.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16708987
The first question was:
1) How do I set the character set for the import utility? What am I doing wrong? I'm a bit confused here. This is the most important question.

All discussions here were devoted how to avoid the fatal Export/Import error - NLS_LANG variable is either not set or set wrong.
0
 
LVL 6

Expert Comment

by:makhan
ID: 16715287
You can set the NLS_LANG parameter in batch file.

We have been using a batch file in windows to achieve this.

regards,

makhan
0
 

Author Comment

by:kencrest
ID: 16719570
....thx guys...your comments helped alot...Everything seems to be good.

Thx
Ken
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month9 days, 9 hours left to enroll

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question