ITPub博客

首页 > Linux操作系统 > Linux操作系统 > NLS considerations in Import/Export - Frequently Asked Questions

NLS considerations in Import/Export - Frequently Asked Questions

原创 Linux操作系统 作者:xhailiang 时间:2007-02-02 00:00:00 0 删除 编辑
Subject: NLS considerations in Import/Export - Frequently Asked Questions
Doc ID: Note:227332.1Type: BULLETIN
Last Revision Date: 29-JAN-2007Status: PUBLISHED

Globalization (NLS): NLS considerations in Import/Export - Frequently Asked Questions
-------------------------------------------------------------------------------------
For the main Globalization (NLS) FAQ please see:
Note 60134.1 Globalization (NLS) - Frequently Asked Questions

1.  How does NLS affect import/export?
2.  How should NLS_LANG be set when using export?
3.  How should NLS_LANG be set when using import?
4.  How is import affected by the NLS_LANGUAGE and NLS_TERRITORY ?
5.  I have the message "( possible ncharset conversion )" during import.
6.  How to know in what characterset a dmp (export) file is created?  
7.  What causes ORA-01401: inserted value too large for column during import ?

For the main exp/imp FAQ please see:
Note 175624.1 Oracle Server - Export and Import FAQ

Globalization (NLS): NLS considerations in Import/Export - Frequently Asked Questions
-------------------------------------------------------------------------------------
1.  How does NLS affect import/export?

    Import and export are client products, in the same way as SQL*Plus or
    Oracle Forms, and will therefore translate characters from the database
    character set to that defined by NLS_LANG. The character set used for
    the export will be stored in the export file and, when the file is imported,
    the import will check the character set that was used. If it is different to
    that defined by NLS_LANG at the import site, the characters will be
    translated to the import character set and then, if necessary, to the
    database character set.

    References:
    Note 15095.1  Export/Import and NLS Considerations
    Note 48644.1  Identifying the Export Character Set

2.  How should NLS_LANG be set when using export?

    Oracle recommendeds to set the character set part of NLS_LANG environment parameter
    to the same character set as the character set of the database you are exporting.

    select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

    That way no conversion will take place and the exportfile will be created
    in the same character set as the orginal database and contain ALL data from
    original database (even incorrectly stored data if that would be the case). 
    Even if the plan is to import this into a database with a different character set
    later the conversion can be postponed until the import.

    Note that this has no relation with the Operating system. If your have a 
    WE8MSWIN1252 database on a unix server (wich is totally supported) then you
    should set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before export.

    During *interaction* with the database (= sqlplus) you need to configure 
    your *unix* client properly and that cannot be 1252 seen *unix* does not 
    has a 1252 characterset 
    Note 264157.1 The correct NLS_LANG setting in Unix Environments


3.  How should NLS_LANG be set when using import?

    If the source and target database have the same character set, 
    the character set part of the NLS_LANG should be set to that same character set
    on both the export and the import.

    Even if the character sets of the exporting and importing databases
    are not the same the best (prefered) value to use for 
    the character set part of NLS_LANG on both export and import is still
    the character set of the source database.

    select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

    But setting the NLS_LANG to the character set of the target database during import
    is also correct. 

    That way conversion only takes place once, either on export or on import.

    However, the preferred place to do the conversion is between the import executable
    and the target database.

    Note that this has no relation with the Operating system. If your source 
    database is a WE8MSWIN1252 database then you simply should set NLS_LANG 
    to AMERICAN_AMERICA.WE8MSWIN1252 before import.

    During *interaction* with the database (= sqlplus) you need to configure 
    your *unix* client properly and that cannot be 1252 seen *unix* does not 
    has a 1252 characterset 
    Note 264157.1 The correct NLS_LANG setting in Unix Environments


4.  Example: you want to go from an WE8MSWIN1252 to an UTF8 db:

    (note that this is only the exp/imp example, if you want to migrate to UTF8
     check Note 260192.1 for the full story )

    1) double check the NLS_CHARACTERSET on the SOURCE database

        select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

       and export with the NLS_LANG set to AMERICAN_AMERICA.

       In this case we want to create a export file containing WE8MSWIN1252 data.

       (This is also the setting you want to use if you take an
       export as backup)

       on unix this is:
       $ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
       $ export NLS_LANG
       $ exp ....

       on windows this is:

       c:>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
       c:>exp ....

    2) import with the NLS_LANG set to American_america.WE8MSWIN1252 (= source NLS_CHARACTERSET)
       into the new UTF8 db.

       on unix this is:
       $ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
       $ export NLS_LANG
       $ imp ....

       on windows this is:

       c:>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
       c:>imp ....


       The conversion to UTF8 is done while inserting the data
       in the UTF8 database.

    We recommend to set the NLS_LANG explicit in the current shell for unix
    (-> Note 131207.1 How to Set Unix Environment Variable )
    or in the dos box used for the exp or imp tool on windows.
    (-> "c:>set NLS_LANG=AMERICAN_AMERICA.")

4.  How is import affected by the NLS_LANGUAGE and NLS_TERRITORY ?

    Not. Normally you use the AMERICAN_AMERICA default, but 
    if you imported with NLS_LANG set to FRENCH_FRANCE for example
    then you will not have problems, even if the originating enviroment
    used GERMAN_GERMANY or so.

5.  I have the message "( possible ncharset conversion )" during import.

    you see something similar to

     Export file created by EXPORT:V08.01.07 via direct path 
     import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set 
     export server uses WE8ISO8859P15 NCHAR character set (possible ncharset conversion)

    in the import log, please see point 11 in
    Note 276914.1 The National Character Set in Oracle 9i and 10g

6.  How to know in what characterset a dmp (export) file is created?

    simply issue: imp system/oracle@database show=yes file=test.dmp

    the output gives you

    import done in US7ASCII character set and AL16UTF16 NCHAR character set
      -> this is the current NLS_LANG value set in the enviroment 
      and the NCHAR characterset of the target database

    import server uses WE8MSWIN1252 character set (possible charset conversion)
      -> this is only shown if the NLS_LANG during this import session is different 
      from the target database characterset, so if you see 3 lines you might have problems :-)

    export client uses UTF8 character set (possible charset conversion)
      -> this is the characterset used during the export session and the
      characterset used in the dmp file.

7.  What causes ORA-01401: inserted value too large for column during import ?

    This is seen when exporting from a database with a 8 bit NLS_CHARACTERSET 
    (like WE8ISO8859P1, WE8MSWIN1252 , WE8DEC ...) or 16 bit NLS_CHARACTERSET 
    (like JA16SJIS , ZHS16GBK, KO16MSWIN949) to a database with a 
    NLS_CHARACTERSET set to AL32UTF8 or UTF8

    (AL32)UTF8 uses more BYTES to store a character then a 8 and 16 bit charactersets.
    This is explained in Note 119119.1 UTF8 Database Character Set Implications
    CHAR and VARCHAR2 colum sizes are defined by default in BYTES not characterst.

    The best solution is to precreate the tables using CHAR semantics (9i and up)
    Note 144808.1 Examples and limits of BYTE and CHAR semantics usage

    



Other Known Problems:
---------------------
Note 278980.1 Oracle10g Import Errors like IMP-00008 / IMP-00009 if Eported with NLS_LANG=FRENCH_FRANCE.[characterset]


References
----------

For further NLS / Globalization information you may start here:
Note 267942.1 - Globalization Technology (NLS) Knowledge Browser Product Page
Note 60134.1  Globalization (NLS) - Frequently Asked Questions

Further details of issues disussed in this note can be found in:
Note 15095.1 Export/Import and NLS Considerations
Note 48644.1 Identifying the Export Character Set

Note 175624.1 Oracle Server - Export and Import FAQ



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171968/,如需转载,请注明出处,否则将追究法律责任。

上一篇: export and import
请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    425187