About DATABASE

Index of This Note:

-------------------


1) What is the National Character Set?

2) Which datatypes use the National Character Set?

3) How to know if I use N-type columns?

4) Should I worry when I upgrade from 8i or lower to 9i, 10g or 11g?

5) The NLS_NCHAR_CHARACTERSET is NOT changed to UTF8 or AL16UTF16 after upgrading to 9i.

6) Can I change the AL16UTF16 to UTF8 / I hear that there are problems with AL16UTF16.

7) Is the AL32UTF8 problem the same as the AL16UTF16 / do I need the same patches?

8) But I still want <characterset> as NLS_NCHAR_CHARACTERSET, like I had in 8(i)!

9) Do i need to set NLS_LANG to AL16UTF16 when creating/using the NLS_NCHAR_CHARACTERSET ?

10) I try to use AL32UTF8 as NLS_NCHAR_CHARACTERSET but it fails with ORA-12714 

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

12) Can i use AL16UTF16 as  NLS_CHARACTERSET ?

13) I'm inserting <special character> in a Nchar or Nvarchar2 col but it comes back as ? ...

14) Do i need to change the NLS_NCHAR_CHARACTERSET in 8i to UTF8 BEFORE upgrading to 9i/10g?

15) Having a UTF8 NLS_CHARACTERSET db is there a advantage to use AL16UTF16 N-types ?

16) I have a message in the DBUA (Database Upgrade Assistant) about NCHAR type when upgrading from 8i..

17) How to go from an UTF8 NLS_NCHAR_CHARTERSET to AL16UTF16?


1) What is the National Character Set?

--------------------------------------


 The National Character set (NLS_NCHAR_CHARACTERSET) is a character set

 which is defined in addition to the (normal) database character set and 

 is used for data stored in NCHAR, NVARCHAR2 and NCLOB columns.


 Your current value for the NLS_NCHAR_CHARACTERSET can be found with this select:


  select value from NLS_DATABASE_PARAMETERS where parameter='NLS_NCHAR_CHARACTERSET';


 You cannot have more than 2 charactersets defined in Oracle:

 The NLS_CHARACTERSET is used for CHAR, VARCHAR2, CLOB columns;

 The NLS_NCHAR_CHARACTERSET is used for NCHAR, NVARCHAR2, NCLOB columns.


 NLS_NCHAR_CHARACTERSET is defined when the database is created 

 and specified with the CREATE DATABASE command.

 The NLS_NCHAR_CHARACTERSET defaults to AL16UTF16 if nothing is specified.


 From 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values:

 UTF8 or AL16UTF16 who are Unicode charactersets.

 See Note 260893.1 Unicode character sets in the Oracle database

 for more info about the difference between them.


 Al lot of people think that they *need* to use the NLS_NCHAR_CHARACTERSET to have

 UNICODE support in Oracle, this is not true, NLS_NCHAR_CHARACTERSET (NCHAR, NVARCHAR2)

 is in 9i always Unicode but you can perfectly use "normal" CHAR and VARCHAR2 columns for storing unicode 

 in a database who has a AL32UTF8 / UTF8 NLS_CHARACTERSET.

 See also point 15.


 When trying to use another NATIONAL characterset the CREATE DATABASE command will

 fail with "ORA-12714 invalid national character set specified"


 The character set identifier is stored with the column definition itself.


2) Which datatypes use the National Character Set?

--------------------------------------------------


 There are three datatypes which can store data in the national character set:


NCHAR     - a fixed-length national character set character string. 

            The length of the column is ALWAYS defined in characters 

            (it always uses CHAR semantics)


NVARCHAR2 - a variable-length national character set character string.  

            The length of the column is ALWAYS defined in characters 

            (it always uses CHAR semantics)


NCLOB     - stores national character set data of up to four gigabytes.     

	    Data is always stored in UCS2 or AL16UTF16, even if the 

	    NLS_NCHAR_CHARACTERSET is UTF8.

	    This has very limited impact, for more info about this please see:

	    Note 258114.1 Possible action for CLOB/NCLOB storage after 10g upgrade

	    and if you use DBMS_LOB.LOADFROMFILE see 

	    Note 267356.1 Character set conversion when using DBMS_LOB


 If you don't know what CHAR semantics is, then please read

 Note 144808.1 Examples and limits of BYTE and CHAR semantics usage


 If you use N-types, DO use the (N'...') syntax when coding it so that Literals are 

 denoted as being in the national character set by prefixing letter 'N', 

 for example:


   create table test(a nvarchar2(100));

   insert into test values(N'this is a NLS_NCHAR_CHARACTERSET string');




3) How to know if I use N-type columns?

---------------------------------------


 This select list all tables containing a N-type column:


  select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB');


 On a 9i database created without (!) the "sample" shema you will see these rows (or less) returned:


OWNER                          TABLE_NAME

------------------------------ ------------------------------

SYS                           ALL_REPPRIORITY

SYS                           DBA_FGA_AUDIT_TRAIL

SYS                           DBA_REPPRIORITY

SYS                           DEFLOB

SYS                           STREAMS$_DEF_PROC

SYS                           USER_REPPRIORITY

SYSTEM                         DEF$_LOB

SYSTEM                         DEF$_TEMP$LOB

SYSTEM                         REPCAT$_PRIORITY


9 rows selected.


  These SYS and SYSTEM tables may contain data if you are using:


  * Fine Grained Auditing -> DBA_FGA_AUDIT_TRAIL 

  * Advanced Replication -> ALL_REPPRIORITY, DBA_REPPRIORITY, USER_REPPRIORITY

                            DEF$_TEMP$LOB , DEF$_TEMP$LOB and REPCAT$_PRIORITY

  * Advanced Replication or Deferred Transactions functionality -> DEFLOB

  * Oracle Streams -> STREAMS$_DEF_PROC




 If you do have created the database with the DBCA and included

 the sample shema then you will see typically:


OWNER                         TABLE_NAME                                        

------------------------------------------------------------                    

OE                           BOMBAY_INVENTORY                                  

OE                           PRODUCTS                                          

OE                           PRODUCT_DESCRIPTIONS                              

OE                           SYDNEY_INVENTORY                                  

OE                           TORONTO_INVENTORY                                 

PM                           PRINT_MEDIA                                       

SYS                          ALL_REPPRIORITY                                   

SYS                          DBA_FGA_AUDIT_TRAIL                               

SYS                          DBA_REPPRIORITY                                   

SYS                          DEFLOB                                            

SYS                          STREAMS$_DEF_PROC                                 

SYS                          USER_REPPRIORITY                                  

SYSTEM                        DEF$_LOB                                          

SYSTEM                        DEF$_TEMP$LOB                                     

SYSTEM                        REPCAT$_PRIORITY                                  


15 rows selected.


 The OE and PM tables contain just sample data and can be dropped if needed.


4) Should I worry when I upgrade from 8i or lower to 9i, 10g or 11g?

--------------------------------------------------------------------


* When upgrading from version 7:


    The National Character Set did not exist in version 7, 

    so you cannot have N-type columns.

    Your database will just have the -default- AL16UTF16 NLS_NCHAR_CHARACTERSET

    declaration and the standard sys/system tables.

    So there is nothing to worry about...


* When upgrading from version 8 and 8i:


  - If you have only the SYS / SYSTEM tables listed in point 3)

    then you don't have USER data using N-type columns.


    Your database will just have the -default- AL16UTF16 NLS_NCHAR_CHARACTERSET

    declaration after the upgrade and the standard sys/system tables.

    So there is nothing to worry about...


    We recommend that you follow this note:

    Note 159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i


  - If you have more tables then the SYS / SYSTEM tables listed in point 3) 

    (and they are also not the "sample" tables) then there are 3 possible cases:


  * Again, the next to points are *only* relevant when you DO have n-type USER data *

  

    a) Your current 8 / 8i NLS_NCHAR_CHARACTERSET is in this list:


       JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED

       KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED

       ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED

       ZHT32EUCFIXED


       Then the new NLS_NCHAR_CHARACTERSET will be AL16UTF16

       and your data will be converted to AL16UTF16 during the upgrade.


       We recommend that you follow this note:

       Note 159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i


    b) Your current 8 / 8i NLS_NCHAR_CHARACTERSET is UTF8:


       Then the new NLS_NCHAR_CHARACTERSET will be UTF8

       and your data not be touched during the upgrade.


       We still recommend that you follow this note:

       Note 159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i


    c) Your current 8 / 8i NLS_NCHAR_CHARACTERSET is NOT in the list of point a) 

       and is NOT UTF8:


       Then your will need to export your data and drop it before upgrading.

       We recommend that you follow this note: 

       Note 159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i


   For more info about the National Character Set in Oracle8 see Note 62107.1


5) The NLS_NCHAR_CHARACTERSET is NOT changed to UTF8 or AL16UTF16 after upgrading to 9i.

----------------------------------------------------------------------------------------


 That may happen if you have not set the ORA_NLS33/ORA_NLS10 environment parameter 

 correctly to the 9i/10g Oracle_Home during the upgrade.

  Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.


 You may see errors like "ORA-12714: invalid national character set specified" 


 We recommend that you follow this note for the upgrade: 

 Note 159657.1 Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i


 Strongly consider to restore your backup and do the migration again or log a 

 TAR, refer to this note and ask to assign the TAR to the NLS/globalization 

 team. That team can then assist you further.


 However please do note that not all situations can be corrected,

 so you might be asked to do the migration again...


Note: do NOT use the UTLNCHAR.SQL or N_SWITCH.SQL script! 

      This will not help and make things only worse...


Provide the output of this select:


select distinct OWNER, TABLE_NAME, DATA_TYPE from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB');


when logging a SR.


6) Can I change the AL16UTF16 to UTF8 / I hear that there are problems with AL16UTF16.

--------------------------------------------------------------------------------------


6a) If you do *not* use N-types then there is NO problem at all with AL16UTF16

   because you are simply not using it and we strongly advice you the keep 

   the default AL16UTF16 NLS_NCHAR_CHARACTERSET.


6b) If you *do* use N-types then there will be a problem with 8i clients and 

   lower accessing the N-type columns (note that you will NOT have a problem 

   selecting from "normal" non-N-type columns).

   More info about that is found there:

   Note 140014.1 ALERT Oracle8/8i to Oracle9i/10g using New "AL16UTF16" National Character Set

   Note 236231.1 New Character Sets Not Supported For Use With Developer 6i And Older Versions


   If this is a situation you find yourself in we recommend to simply use UTF8 

   as NLS_NCHAR_CHARACTERSET or create a second 9i db using UTF8 as NCHAR and use this as "inbetween" between the 8i and the 9i db

   you can create views in this new database that do a select from the AL16UTF16 9i db

   the data will then be converted from AL16UTF16 to UTF8 in the "inbetween" database and that can 

   be read by oracle 8i


   This is one of the 2 reasons why you should use UTF8 as NLS_NCHAR_CHARACTERSET.

   If you are NOT using N-type columns with pre-9i clients then there is NO reason to go to UTF8.


6c) If you want to change to UTF8 because you are using transportable tablespaces from 8i database

   then check if are you using N-types in the 8i database that are included in the tablespaces that you are transporting.


     select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB');


   If yes, then you have the second reason to use UTF8 as as NLS_NCHAR_CHARACTERSET.


   If not, then leave it to AL16UTF16 and log a tar for the solution of the ORA-19736

   and refer to this document. (see also point 8 in this note)


6D) You are in one of the 2 situations where it's really needed to change from

   AL16UTF16 to UTF8 then the correct steps to go from AL16UTF16 to UTF8 are:


6D1) install csscan:


Note 458122.1 Installing and configuring CSSCAN in 8i and 9i

Note 745809.1 Installing and configuring CSSCAN in 10g and 11g


6D2) For 9i:


   * export all the user N-data


   * drop/truncate all the user N-data

   -- If you do not drop all N-data then you will run into

   -- ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists


   * run csscan to check if everything is ok


   csscan FULL=Y  TONCHAR=UTF8 LOG=check CAPTURE=Y ARRAY=1000000 PROCESS=2


   aways run csscan with / as sydba


   * csscan will ask:




     Current database character set is UTF8.  <- this is the current NLS_CHARACTERSET


     Enter new database character set name: > <-*just hit enter here*




   * check that you see in the check.txt file this:


   All character type data in the data dictionary remain the same in the new character set

   All character type application data remain the same in the new character set




   * then you can do a ALTER DATABASE NATIONAL CHARACTER SET UTF8;

    Shutdown the listener and any application that connects locally to the database.

    There should be only ONE connection the database during the WHOLE time and that's

    the sqlplus session where you do the change.


    1. Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all.

       If you are using RAC see 

       Note 221646.1 Changing the Character Set for a RAC Database Fails with an ORA-12720 Error


    2. Execute the following commands in sqlplus connected as "/ AS SYSDBA":


       SPOOL Nswitch.log

       SHUTDOWN IMMEDIATE;

       STARTUP MOUNT;  

       ALTER SYSTEM ENABLE RESTRICTED SESSION; 

       ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 

       ALTER SYSTEM SET AQ_TM_PROCESSES=0; 

       ALTER DATABASE OPEN; 

       ALTER DATABASE NATIONAL CHARACTER SET UTF8;

       SHUTDOWN IMMEDIATE;


    3. Restore the parallel_server parameter in INIT.ORA, if necessary.


   * import the user N-data again 


6D3) for 10g and up:


   * export any user N-data

   * drop/truncate any user N-data

   * Truncate these 2 xdb tables if


    SQL>select LOCALNAME from XDB.XDB$QNAME_ID;

    SQL>select NMSPCURI from XDB.XDB$NMSPC_ID;


    gives 7 rows


    SQL>truncate table XDB.XDB$QNAME_ID

    SQL>truncate table XDB.XDB$NMSPC_ID


   if you have more rows log a SR.


   * run csscan to check if everything is ok


   csscan FULL=Y  TONCHAR=UTF8 LOG=check CAPTURE=Y ARRAY=1000000 PROCESS=2


   always run csscan with / as sydba


   * csscan will ask:




     Current database character set is UTF8.  <- this is the current NLS_CHARACTERSET


     Enter new database character set name: > <-*just hit enter here*




  * check that you see in the check.txt file this:


   All character type data in the data dictionary remain the same in the new character set

   All character type application data remain the same in the new character set


  * after that run csalter ( using alter database is in a 10g system for the national characterset not really a problem, but csalter is the official way)


  Shutdown the listener and any application that connects locally to the database.

  There should be only ONE connection the database during the WHOLE time and that's

  the sqlplus session where you do the change.


  Then you do in sqlplus connected as "/ AS SYSDBA":




   --  Make sure the parallel_server and CLUSTER_DATABASE parameter are set 

   --  to false or it is not set at all.

   --  If you are using RAC you will need to start the database in single instance

   --  with CLUSTER_DATABASE = FALSE

   sho parameter CLUSTER_DATABASE

   sho parameter PARALLEL_SERVER

   -- check if you are using spfile

   sho parameter pfile

   -- if this "spfile" then you are using spfile

   -- in that case note the 

   sho parameter job_queue_processes

   sho parameter aq_tm_processes

   -- (this is Bug 6005344 fixed in 11g )

   -- then do


   shutdown immediate

   startup restrict

   SPOOL Nswitch.log

    @@?\rdbms\admin\csalter.plb

   -- if you are using spfile then you need to also

   -- ALTER SYSTEM SET job_queue_processes=<original value> SCOPE=BOTH;

   -- ALTER SYSTEM SET aq_tm_processes=<original value> SCOPE=BOTH;


   shutdown

   startup




* after this update the XDB tables again with these inserts:


(these inserts can also be found in the $ORACLE_HOME/rdbms/admin/catxdbtm.sql script)


insert into xdb.xdb$nmspc_id values ('http://www.w3.org/XML/1998/namespace',          HEXTORAW('01'));

insert into xdb.xdb$nmspc_id values ('http://www.w3.org/XML/2000/xmlns',              HEXTORAW('02'));

insert into xdb.xdb$nmspc_id values ('http://www.w3.org/2001/XMLSchema-instance',     HEXTORAW('03'));

insert into xdb.xdb$nmspc_id values ('http://www.w3.org/2001/XMLSchema',              HEXTORAW('04'));

insert into xdb.xdb$nmspc_id values ('http://xmlns.oracle.com/2004/csx',              HEXTORAW('05'));

insert into xdb.xdb$nmspc_id values ('http://xmlns.oracle.com/xdb',                   HEXTORAW('06'));

insert into xdb.xdb$nmspc_id values ('http://xmlns.oracle.com/xdb/nonamespace',       HEXTORAW('07'));


insert into xdb.xdb$qname_id values (HEXTORAW('01'), 'space', HEXTORAW('01'), HEXTORAW('10'));

insert into xdb.xdb$qname_id values (HEXTORAW('01'), 'lang', HEXTORAW('01'), HEXTORAW('11'));

insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'type', HEXTORAW('01'), HEXTORAW('12'));

insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'nil', HEXTORAW('01'), HEXTORAW('13'));

insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'schemaLocation', HEXTORAW('01'), HEXTORAW('14'));

insert into xdb.xdb$qname_id values (HEXTORAW('03'), 'noNamespaceSchemaLocation', HEXTORAW('01'), HEXTORAW('15'));

insert into xdb.xdb$qname_id values (HEXTORAW('02'), 'xmlns', HEXTORAW('01'), HEXTORAW('16'));


commit;


* import any user N-data




important:


   Do NOT use the UTLNCHAR.SQL or N_SWITCH.SQL script.

   Using this a to try to go from UTF8 to AL16UTF16 (or inverse)

   will corrupt existing NCHAR data !!!!!!

   It is to be used only in specific conditions when upgrading from 8i to 9i


7) Is the AL32UTF8 problem the same as the AL16UTF16 / do I need the same patches?

----------------------------------------------------------------------------------

 No, they may look similar but are 2 different issues.


 For information about the possible AL32UTF8 issue please see 

  Note 237593.1 Problems connecting to AL32UTF8 databases from older versions (8i and lower) 

 

8) But I still want <characterset> as NLS_NCHAR_CHARACTERSET, like I had in 8(i)!

---------------------------------------------------------------------------------


 This is simply not possible.


 From 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values: UTF8 or AL16UTF16.


 Both UTF8 and AL16UTF16 are unicode charactersets, so they can

 store whatever <characterset> you had as NLS_NCHAR_CHARACTERSET in 8(i).


 If you are not using N-types then keep the default AL16UTF16 (or use UTF8 if you really want),

 it doesn't matter if you don't use the N-types.


 There is one condition in which this "limitation" can have a undisired affect,

 when you are importing an Oracle8i Transportable Tablespace into Oracle9i

 you can run into a ORA-19736 (as wel with AL16UTF16 as with UTF8).


 Simply provide the 8i output of:


  select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB');


 In that case log a TAR, refer to this note and ask to assign the TAR to the 

 NLS/globalization team. That team can then assist you to work around this 

 issue in a easy way.

 Do NOT try to change the national characterset in 8i to AL16UTF16 or update system tables

 like you find sometimes on "oracle dba sites" on the internet if you don't want to

 test your backup strategy.


9) Do i need to set NLS_LANG to AL16UTF16 when creating/using the NLS_NCHAR_CHARACTERSET ?

------------------------------------------------------------------------------------------


As clearly stated in 

 Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)

point "1.2 What is this NLS_LANG thing anyway?"


* NLS_LANG is used to let Oracle know what characterset you client's OS is USING

  so that Oracle can do (if needed) conversion from the client's characterset to the 

  database characterset.


NLS_LANG is a CLIENT parameter has has no influence on the database side.


10) I try to use AL32UTF8 as NLS_NCHAR_CHARACTERSET but it fails with ORA-12714 

-------------------------------------------------------------------------------


 From 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values:

 UTF8 or AL16UTF16.


UTF8 is possible so that you can use it (when needed) for 8.x backwards compatibility.

In all other conditions AL16UTF16 is the preferred and best value.

AL16UTF16 has the same unicode revision as AL23UTF8, 

so there is no need for AL32UTF8 as NLS_NCHAR_CHARACTERSET.


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

------------------------------------------------------------------------


in the import log you see something similar to this:


Import:  Release 9.2.0.4.0 - Production on Fri Jul 9 11:02:42 2004 

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

  

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production 

JServer Release 9.2.0.4.0 - Production 

 Export file created by EXPORT:V08.01.07 via direct path 

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set 

export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)


This is normal and is not a error condition.


- If you do not use N-types then this is a pure informative message.


- But even in the case that you use N-types like NCHAR or NCLOB then this is not a problem:


  * the database will convert from the "old" NCHAR characterset to the new one automatically.

    (and - unlike the "normal" characterset - the NLS_LANG has no impact on this conversion

     during exp/imp)


  * AL16UTF16 or UTF8 (the only 2 possible values in 9i) are unicode characterset and so

    can store any character... So no data loss is to be expected.


12) Can i use AL16UTF16 as NLS_CHARACTERSET ?

----------------------------------------------


No, AL16UTF16 can only be used as  NLS_NCHAR_CHARACTERSET in 9i and above.

Trying to create a database with a AL16UTF16 NLS_CHARACTERSET will fail.


13) I'm inserting <special character> in a Nchar or Nvarchar2 col but it comes back as ? ...

--------------------------------------------------------------------------------------------------


see point 14 in  Note 227330.1 Character Sets & Conversion - Frequently Asked Questions


14) Do i need to change the NLS_NCHAR_CHARACTERSET in 8i to UTF8 BEFORE upgrading to 9i/10g?

--------------------------------------------------------------------------------------------


No, see point 4) in this note.


15) Having a UTF8 NLS_CHARACTERSET db is there a advantage to use AL16UTF16 N-types ?

-------------------------------------------------------------------------------------


There might be 2 reasons:


a) one possible advantage is storage (disk space) for NCHAR/NVARCHAR2.


UTF8 uses 1 up to 3 bytes, AL16UTF16 always 2 bytes.


If you have a lot of non-western data (cyrillic, Chinese, Japanese, Hindi languages..)

then it can be advantageous to use N-types for those columns seen those characters will use

3 bytes in UTF8 and 2 bytes in AL16UTF16.


For western data (english, french, spanish, dutch, german, portuguese etc...)

UTF8 will use in most cases less disk space then AL16UTF16.


 Note 260893.1 Unicode character sets in the Oracle database


This is NOT true for NCLOB and CLOB, they are both encoded a internal fixed-width Unicode character set

 Note 258114.1 Possible action for CLOB/NCLOBrage after 10g upgrade

so they will use the same amount of disk space.


b) other possible advantage is extending the limits of CHAR semantics


   For a single-byte character set encoding, the character and byte length are

   the same. However, multi-byte character set encodings do not correspond to 

   the bytes, making sizing the column more difficult.


   Hence the reason why CHAR semantics was introduced. However, we still have some

   physical underlying byte based limits and development has choosen to allow full usage

   of the underlying limits. This results in the following table giving the maximum amount 

   of CHARarcters occupying the MAX datalength that can be stored for a certain 

   datatype in 9i and up.


   The MAX colum is the MAXIMUM amount of CHARACTERS that can be stored 

   occupying the MAXIMUM data length. Seen that UTF8 and AL32UTF8 are VARRYING 

   charactersets this means that a string of X chars can be X to X*3 (or X*4 for AL32) bytes.


   The MIN col is the maximum size that you can *define* and that Oracle can store if all data

   is the MINIMUM datalength (1 byte for AL32UTF8 and UTF8) for that characet.


   N-types (NVARCHAR2, NCHAR) are *always* defined in CHAR semantics, you cannot define them in BYTE.


   All numbers are CHAR definitions.




                UTF8 (1 to 3 bytes)  AL32UTF8 (1 to 4 bytes)  AL16UTF16 ( 2 bytes)

                MIN       MAX         MIN      MAX             MIN      MAX

   CHAR         2000      666        2000      500             N/A      N/A


   VARCHAR2     4000     1333        4000     1000             N/A      N/A


   NCHAR        2000      666         N/A      N/A            1000     1000


   NVARCHAR2    4000     1333         N/A      N/A            2000     2000


                (N/A means not possible)


   This means that if you try to store more then 666 characters

   that occupy 3 bytes in UTF8 in a CHAR UTF8 colum you still will get a

   ORA-01401: inserted value too large for column

   (or from 10g onwards: ORA-12899: value too large for column )

   error, even if you have defined the colum as CHAR (2000 CHAR) 

   so here it might be a good idea to define that column as NCHAR

   that will raise the MAX to 1000 char's ...


   Note 144808.1 Examples and limits of BYTE and CHAR semantics usage


Disadvantages using N-types:


* You might have some problems with older clients if using AL16UTF16

  see point 6) b) in this note

* Be sure that you use (AL32)UTF8 as NLS_CHARACTERSET , otherwise you will run into

  point 13 of this note.

* Do not expect a higher *performance* by using AL16UTF16, it might be faster

  on some systems, but that has more to do with I/O then with the database kernel.

* Forms 6i/9i does not support NCHAR / NVARCHAR2. 

  This is documented in the 6i Forms online help. 

  and in Note 258195.1 Oracle9 Features not yet Supported in Forms 9i

* If you use N-types, DO use the (N'...') syntax when coding it so that Literals are 

  denoted as being in the national character set by prepending letter 'N', for example:

   

   create table test(a nvarchar2(100));

   insert into test values(N'this is NLS_NCHAR_CHARACTERSET string');


Normally you will choose to use Char/Varchar2 (using a (AL32)UTF8 NLS_CHARACTERSET)

for simplicity, to avoid confusion and possible other limitations who might be

imposed by your application or programming language to the usage of N-types.


16) I have a message running DBUA (Database Upgrade Assistant) about NCHAR type when upgrading from 8i .

--------------------------------------------------------------------------------------------------------


see point 16 in  Note 227330.1 Character Sets & Conversion - Frequently Asked Questions


17) How to go from an UTF8 NLS_NCHAR_CHARTERSET to AL16UTF16?

-------------------------------------------------------------


  Befor going from UTF8 to AL16UTF16 you need to see that you don't have any 

  NCHAR bigger then 1000 CHAR or NVARCHAR2 column bigger then 2000 CHAR 


   select distinct OWNER, TABLE_NAME, COLUMN_NAME, CHAR_LENGTH from DBA_TAB_COLUMNS where DATA_TYPE='NCHAR' and CHAR_LENGTH > 1000;

   select distinct OWNER, TABLE_NAME, COLUMN_NAME, CHAR_LENGTH from DBA_TAB_COLUMNS where DATA_TYPE='NVARCHAR2' and CHAR_LENGTH > 2000;


 or the ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16; / Csalter will fail


You will need to adapt those columns after exporting the User N-data (if any).

 

   you can ignore for the moment SYS.DBA_FGA_AUDIT_TRAIL|SQL_TEXT if this is 4000 and change to AL16UTF16

   -> select CHAR_LENGTH from DBA_TAB_COLUMNS where OWNER='SYS' and TABLE_NAME='DBA_FGA_AUDIT_TRAIL' and COLUMN_NAME='SQL_TEXT';

   And then after the change to AL16UTF16 run SQL> @?\rdbms\admin\catfga.sql 


  The procedure is just the same as in point 6D) simply use AL16UTF16 instead of UTF8 as characterset.


Related Documents:

------------------

Note 62107.1  The National Character Set in Oracle8

Note 227330.1	Character Sets & Conversion - Frequently Asked Questions

Note 227332.1 NLS considerations in Import/Export - Frequently Asked Questions


Note 158577.1 NLS_LANG Explained (How does Client-Server nversion Work?)

Note 159657.1	Complete Upgrade Checklist for Manual Upgrades from 8.X.0.1 to Oracle9i

Note 278725.1 utlnchar.sql and n_switch.sql fail with ORA-01735 on "non-standard" object names

Note 260893.1 Unicode character sets in the Oracle database

Note 144808.1 Examples and limits of BYTE and CHAR semantics usage


Note 237593.1 Problems connecting to AL32UTF8 databases from older versions (8i and lower) 

Note 140014.1 ALERT Oracle8/8i to Oracle9i using New "AL16UTF16" National Character Set

Note 236231.1 New Character Sets Not Supported For Use With Developer 6i And Older Versions


Note 258114.1 Possible action for CLOB/NCLOB storage after 10g upgrade

Note 267356.1 Character set conversion when using DBMS_LOB


For further NLS / Globalization information you may start here:

Note 60134.1  Globalization Technology (NLS) - Frequently asked Questions

SQL * Plus 명령어  & 환경 시스템 변수

                                                                           - By Everekk79 ( Last Updated 2007.11.22 )

sqlplus [ username [ /password [ @database ]]

SQL*Plus 명령어는 한번에 한 라인만 입력할 수 있으며 명령어를 축약하여 사용할 수 있다.

SQL*Plus 명령어는 SQL 버퍼에 저장되지 않으며 세미콜론(;)과 같은 종료 문자를 사용하지 않는다.

RUN : 최근에 실행된 SQL 명령문을 실행한다

         SQL 명령문과 함께 결과를 출력한다

/ : 최근에 실행된 SQL 명령문을 실행한다.

    검색 결과만을 출력한다.

  ==> 최근에 실행된 SQL 명령문은 SQL 버퍼에 저장되어 있 다.

eixt : 정상 종료

  ==> 정상 종료를 해야만 사용자가 데이터베이스에 수정한 내용이 디스크에 영구적으로 저장된다.

▣ 시스템 변수 설정 명령어

 SET System_Variable Value

Set Auto [Commit] { off | on | imm[ediate] | n }

on, immediate : DML 명령문이 성공적으로 실행되면 자동적으로 Commit 명령문 실행

off : DML 명령문 실행 후, 사용자가 직접 Commit 명령문 실행

n : DML 명령문을 n 번 성공적으로 실행 후 자동적으로 Commit 문 실행 

Set Feed[back] { 6 | n | off | on }

Select 문의 실행 결과를 표시하기 위하여 출력 행의 수를 지정하는 시스템 변수

n : 출력 행의 수가 n 이상인 경우만  " n개의 행이 선택되었습니다" 라는 메시지 출력

6 : 기본값

on : feedback 변수 값을 기본값으로 설정한다.

Set Heading { off | on }

off : 컬럼 제목이 출력되지 않는다.

on : 컬럼 제목을 출력한다.

Set Lin[esize] { 80 | n }

한 화면에 표시되는 SQL 명령문의 출력 결과에 대한 행의 크기를 설정한다.

기본값은 80이며 최대값은 시스템에 따라 차이가 남

Set Pages[ize] { 14 | n }

한 화면에 표시되는 SQL 명령문의 실행 결과에 대한 페이지의 크기를 설정하기 위한 시스템 변수

한 페이지에는 칼럼 제목, 데이터의 구분선, 페이지를 구분하기 위한 공백 라인도 포함된다.

기본값은 14이다,

Set Pause { off | on }

SQL 명령문의 실행 결과를 한 화면에 보기 힘든 경우에 한 페이지씩 나누어 출력하기 위한 시스템 변수이다.

on : 사용자가 엔터키를 입력할 때까지 대기하고 있다가 엔터 키를 입력하면 실행 결과를 출력한다.

Set Term[out] { off | on }

SQL 명령문 실행 결과를 화면에 출력할 지 여부를 지정하기 위한 시스템 변수

스크립트(*.sql) 파일에 의해 실행된 여러 개의 SQL 명령문의 출력 결과를 확인 할 필요가 없는 경우 유용하게 사용된다.

off : SQL 명령문의 실행 결과가 화면에 출력되지 않는다.

on : SQL 명령문의 실행 결과를 화면에 출력한다.

Set Ti[me] { off | on }

SQL  프롬프트 앞에 시스템의 현재 시간을 함께 표시하도록 설정하는 시스템 변수

on : SQL 프롬프트 앞에 시간 정보가 함께 표시된다.

off : 시간 정보를 출력되지 않도록 설정한다.

Set Timing { off | on }

Timing  변수는 SQL 명령문을 실행하는데 소요된 시간을 출력하기 위한 시스템 변수이다.

시간은 '시:분:초.밀리초' 형식으로 출력된다.

on : SQL 명령문을 실행하는데 소요된 시간을 출력한다,

off : 소요된 시간 정보가 출력되지 않는다.

Set Und[erline] { - | c | on | off }

컬럼 제목과 데이터간의 구분 기호를 설정하기 위한 시스템변수이다.

c : 모든 문자

on : 구분기호 사용

off : 구분기호 사용하지 않음

Set Verify { on | off }

SQL 명령문이나 PL/SQL에서 &를 이용한 치환 변수등을 사용할 때 치환되기 전 후의 자세한 값을

보일 건지의 여부를 결정한다. 기본값은 On이다

Set Serveroutput { on | off }

PL/SQL 문에서 dbms_output.put_line() 프로시저를 사용

출력을 SQL*Plus 에서 보려면 Set Serveroutput on 을 먼저 실행해야 한다.

Set Colsep { text }

컬럼이 표시될때 컬럼의 구별 문자

열 사이에 출력되는 문자를 설정

Default Value : 공백

▣ SQL*Plus 기타 명령어

Show { system_variable | all }

Show 명령문은 현재 세션에 설정된 시스템 변수를 확인하기 위한 명령어이다.

Show all을 할 경우 현재 설정된 모든 환경 변수값을 확인 할수 있다.

Help 명령어

SQL 명령어를 모를 경우 사용

▣ 형식 명령어

Column { column | alias } [ option ] [ Format ]

Column 명령어는 SQL 명령문의 실행 결과로 출력되는 칼럼 제목이나 컬럼 데이터에 대한 출력 형식을 다양하게 지정하기 위한 명령어이다.

[ option ]

Cle[ar] : 컬럼 형식 해제

For[mat] fromat : 컬럼 데이터의 출력 형식 지정

Hea[ding] text : 컬럼 제목 설정, text 내의 수직(|)바는 컬럼 제목을 여러 줄로 출력 할 경우

                       EnterKey 역활

Jus[tify] { align } : 컬럼 제목을 왼쪽, 가운데 또는 오른쪽 정렬

Nopri[nt] : 컬럼 숨기기

pri[nt] : 컬럼 출력하기

Nul[l] text : null값에 대한 출력 문자 지정

[ Format ]

An : 문자 형식 컬럼의 출력 크기를 n폭으로 설정

9 :  단일 zero-suppression (0억제) 숫자

      example >> 999999 -> 1234

0 : 지정된 길이만큼 숫자 앞에 0 추가

      example >> 009999 -> 001234

$ : 숫자 앞에 달러 기호 삽입

      example >> $9999 -> $1234

L : 숫자 앞에 지역 화폐단위 삽입

      example >> L9999  -> $1234

. :  소숫점 위치 지정

      example >> 9999.99  -> 1234.00

, : 1000 자리마다 ',' 구분자 삽입

      example >> 9,999 -> 1,234

SQL> help variable

 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

VAR[IABLE] [variable [type]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE

▣ 편집 명령어

A[ppend] text : SQL 버퍼의 현재 라인 끝에 text 추가

C[hange]/old/new : 현재 라인의 old text를 new text로 변경

C[hange]/text/ : 현재 라인에서 text 삭제

Cl[ear] Buff[er] : 모든 라인 삭제

Del : 현재 라인 삭제

Del n : n 번째 라인 삭제

Del m n : m 번째 라인에서 n 라인까지 Text 삭제

I[nput] : 현재 라인 다음에 text 추가

I[nput] text : 현재 라인 다음에 text 추가

L[ist] : 모든 라인 출력

L[ist] n : n번째 라인의 text 출력

L[ist] m n : m번째 라인부터 n 라인까지 text 출력

n : n 번째 라인으로 이동

n text : n 번째 라인 내용으로 text 변경

0 text : 1번째 라인 앞에 text 추가

▣ 파일 조작 명령어

Sav[e] filename : 현재 SQL 버퍼의 내용을 파일에 저장

Get filename :  Save 명령어로 저장한 파일을 SQL 버퍼에 읽어 옴

Sta[rt] filename : 파일을 일고 즉시 실행

@filename : 파일을 읽고 즉시 실행

Ed[it] filename : 저장된 파알 내용을 편집

Spo[ol] [filename | off | on ] : 파일에 출력 결과를 저장

                                            off 는 spool 파일을 닫음

                                            on 은 spool 파일을 닫고 프린터로 파일 전송