Which characterset is best?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Which characterset is best?

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021

    Question Which characterset is best?

    We have a semi-automated install procedure for Oracle 9.2.0.6 on windows 200 Advanced Server. However, the database is getting created with the WE8ISO8859P1 character set and the software is getting installed with the WE8MSWIN1252 character set. This causes the warnings below in export files. All of our databases are english and only need a single byte characters. Would it be better to use the WE8ISO8859P1 character set or the WE8MSWIN1252 character set?

    Thanks.

    Code:
    Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production
    Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
    server uses WE8ISO8859P1 character set (possible charset conversion)
    
    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    . exporting database links
    . exporting sequence numbers
    . exporting directory aliases
    . exporting context namespaces
    . exporting foreign function library names
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions
    . exporting system procedural objects and actions
    . exporting pre-schema procedural objects and actions
    . exporting cluster definitions
    . about to export SYSTEM's tables via Conventional Path ...
    . . exporting table            AQ$_INTERNET_AGENTS          0 rows exported
    . . exporting table       AQ$_INTERNET_AGENT_PRIVS          0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table                    DEF$_AQCALL          0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table                   DEF$_AQERROR          0 rows exported
    . . exporting table                  DEF$_CALLDEST          0 rows exported
    EXP-00091: Exporting questionable statistics.
    EXP-00091: Exporting questionable statistics.
    . . exporting table               DEF$_DEFAULTDEST          0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table               DEF$_DESTINATION          0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table                     DEF$_ERROR          0 rows exported
    EXP-00091: Exporting questionable statistics.
    . . exporting table                       DEF$_LOB          0 rows exported
    ...etc...
    this space intentionally left blank

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Character set depends upon the application requirements.
    Nothing is best, nothing is worst.

    Since you did not set up NLS_LANG environment variable, you see error in export.
    See below:

    host:UAT:SID=MYDB=>exp / tables=tamil.t1

    Export: Release 9.2.0.5.0 - Production on Thu Sep 1 14:03:54 2005

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    server uses WE8MSWIN1252 character set (possible charset conversion)

    About to export specified tables via Conventional Path ...
    Current user changed to TAMIL
    . . exporting table T1 2 rows exported
    Export terminated successfully without warnings.

    [ /opt/GTS/oracle/MYDB/admin/create ]
    host:UAT:SID=MYDB=>

    [ /opt/GTS/oracle/MYDB/admin/create ]
    host:UAT:SID=MYDB=>export NLS_LANG=American_America.WE8MSWIN1252

    [ /opt/GTS/oracle/MYDB/admin/create ]
    host:UAT:SID=MYDB=>exp / tables=tamil.t1

    Export: Release 9.2.0.5.0 - Production on Thu Sep 1 14:13:37 2005

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

    About to export specified tables via Conventional Path ...
    Current user changed to TAMIL
    . . exporting table T1 2 rows exported
    Export terminated successfully without warnings.


    Tamil

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    This is the code that runs to generate the database. I plan on removing the CHARACTER SET "WE8ISO8859P1" and NATIONAL CHARACTER SET "UTF8" lines. This should cause the character sets to line up between the database and client. Is there a better solution than this?

    Code:
    set echo on                                                                                                                           
    set feedback on                                                                                                                       
    set termout on                                                                                                                        
    set timing on                                                                                                                         
    startup nomount pfile = "\\pfile\init.ora"                                                      
    CREATE DATABASE ""                                                                                                        
       MAXINSTANCES 1                                                                                                                     
       MAXDATAFILES 254                                                                                                                   
       MAXLOGFILES 32                                                                                                                     
       CHARACTER SET "WE8ISO8859P1"                                                                                                       
       NATIONAL CHARACTER SET "UTF8"                                                                                                      
    DATAFILE '\\system_01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED                          
    UNDO TABLESPACE UNDO_TS DATAFILE '\\undo_01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED   
    DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '\\temp_01.dbf' SIZE 1200M UNIFORM SIZE 1M                   
    EXTENT MANAGEMENT LOCAL                                                                                                               
    LOGFILE GROUP  1 ('\\redo_01a.log') SIZE 100M,                                                           
            GROUP  2 ('\\redo_02a.log') SIZE 100M,                                                           
            GROUP  3 ('\\redo_03a.log') SIZE 100M,                                                           
            GROUP  4 ('\\redo_04a.log') SIZE 100M,                                                           
            GROUP  5 ('\\redo_05a.log') SIZE 100M,                                                           
            GROUP  6 ('\\redo_06a.log') SIZE 100M,                                                           
            GROUP  7 ('\\redo_07a.log') SIZE 100M,                                                           
            GROUP  8 ('\\redo_08a.log') SIZE 100M,                                                           
            GROUP  9 ('\\redo_09a.log') SIZE 100M;                                                           
            GROUP 10 ('\\redo_10a.log') SIZE 100M;                                                           
    spool off                                                                                                                             
    @@cre_dict.sql
    this space intentionally left blank

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    CHARACTER SET "WE8ISO8859P1" is used for SQL text, and the data types such as CHAR, nad VARCHAR2

    The National character set is used for the data types NCHAR, and NVARCHAR2. In general, as with regular character set, it cannot be changed.

    As I said, both usages depend upon the application requirements.
    Assume your database stores both ENGLISH and FRENCH characters. Then probably you need to go for UTF16 or UTF8.

    Tamil

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    We use WE8ISO8859P1 for many European languages. No problem with etc - not sure about Icelandic & Czech extensions . . .
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Jul 2002
    Posts
    335
    There's lots of stuff on metalink explaining the differences.

    If you're looking at WE8ISO8859P1, also consider WE8ISO8859P15, gives you a little bit extra flexibility. But at the end of the day, its dictated by the application.

    Bazza

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width