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

Table생성 스크립트 파일 뽑아내기 

 

작성자 : 한준희

출처 : www.en-core.com 질문과 답변 게시판..




[질문]


현재 DB에 존재하는 Table에 대하여

Create Table .. Storage부분 포함하여

스크립파일로 뽑아낼수 있는 방법이 있나요?





[답변] 


방법은 있습니다.


일단 스텝을 알려드릴테니 한번 해보시길...

( 어제도 이 작업을 통해 스크립트를 뽑았는데..^^)


스크립팅할 유저명과 패스워드를 scott/tiger라 하겠습니다.




1) export를 할때 데이터는 만들지 않고 schema image만 뽑아냅니다.

  (만약 export를 받은 파일이 이미 있으시다면 1번 항목은 생략하셔도 됩니다.)

   

   exp scott/tiger file=exp.dmp compress=n rows=n





2) 1번 스텝과 같이 해서 성공한 export dump파일을 이용하여 create script를 만드는 방법입니다. 

   이 방법은 import라는 유틸리티를 사용하여 작업을 합니다.


   imp scott/tiger file=exp.dmp indexfile=create_table.sql full=y




   

3) 요렇게 하면 create_table.sql이라는게 생성됩니다.

   vi나 편집기로 여시면 REM이라는 코멘트로 앞부분이 막혀 있을 겁니다.

   이걸 모두 없애시면 그야말로 DBMS내에 있는 형태 그대로 만들어진 Table Create Scripts입니다.

CREATE PUBLIC DATABASE LINK "Link명"
CONNECT TO DB이용자ID
IDENTIFIED BY "DB이용자비밀번호"
USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 원격DB IP)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = 원격DB SID)
    )
  )';

LOB, LONG, LONG RAW 데이터 타입 간의 비교


PURPOSE
-------------
이 문서는 LOB 데이터 타입과 LONG이나 LONG Raw 데이터 타입의 차이점 및 LOB 사용에 따라 발생하는 제약 사항을 기술하는 데 목적이 있다.

Explanation
---------------
LOB (Large Object)는 LONG이나, LONG RAW 데이터 타입과 유사하지만, 일부 차이점이 존재한다.

1. 테이블의 한 ROW에 여러 LOB 컬럼이 있을 수 있는 반면, LONG 또는 LONG RAW 컬럼은 한 ROW에 하나 밖에 사용될 수 없다.

2. LOB는 사용자 정의 데이터 타입 (user-defined datatype)의 속성 (attribute) 으로 사용될 수 있는 반면, LONG이나 LONG RAW는 속성으로 사용될 수 없다.

3. LONG 또는 LONG RAW는 값 전체가 테이블 내에 저장이 되는 반면, LOB는 테이블 컬럼
내에 LOB locator만 저장이된다. BLOB과 CLOB (내부 LOB) 데이터는 별도의 테이블스페이스에 저장시킬 수 있으며, BFILE (외부 LOB) 데이터는 데이터베이스 외부의 별도 파일로 존재한다. 따라서 LOB 컬럼을 액세스할 경우에는, locator 값만 return되는 반면, LONG이나 LONG RAW 컬럼을 액세스할 경우에는, 전체 값이 return된다.

4. LOB 데이터는 4GB까지 저장이 가능하며, BFILE 컬럼이 가리키는 파일 크기는 4GB 이내에서 OS에서 지원하는 최대 크기까지 가능하다. 액세스 가능한 범위는 1부터 (232-1) 까지이다. 한편 LONG이나 LONG RAW 데이터 타입에서는 2GB 까지만 지원이 가능하다.

5. 데이터에 대한 랜덤 액세스 기능 또는 데이터 조작을 할 경우 LOB를 사용하는 것이 LONG 또는 LONG RAW를 사용하는 것에 비해 훨씬 많은 기능을 사용할 수 있다.

6. LOB는 랜덤 액세스가 가능한 반면, LONG 타입에 저장된 데이터는 처음부터 원하는 지점까지 순차적으로 읽어 처리하여야 한다.

7. LOB 데이터에 대한 replication을 local 또는 remote에서 수행할 수 있는 반면, LONG / LONG RAW 컬럼 데이터는 replication이 되지 않는다.

8. LONG 컬럼의 데이터는 TO_LOB()라는 함수를 사용하여 LOB로 변환 가능하지만,
LOB를 LONG / LONG RAW로 변환 하는 기능은 제공되지 않는다.



한편, LOB 사용을 하는데 다음과 같은 제약 사항이 따른다.

1. LOB는 클러스터 테이블에서는 사용할 수 없으며, 따라서 클러스터 키로도 사용할 수 없다.

2. LOB 컬럼은 GROUP BY, ORDER BY, SELECT DISTINCT 등에 사용할 수 없으며
JOIN 에도 사용할 수 없다. 그러나 LOB 컬럼을 사용하는 테이블에 대한 UNION ALL은 지원이 된다. UNION MINUS나 SELECT DISTINCT는 OBJECT TYPE의 MAP이나 ORDER 함수가 정의된 경우 사용할 수 있다.

3. LOB 컬럼은 ANALYZE ... COMPUTE/ESTIMATE STATISTICS 명령 사용 시에도 analyze 되지 않는다.

4. LOB는 파티션된 IOT (Index Organized Table)에는 사용할 수 없다. 그러나
파티션 되어 있지 않은 IOT에는 사용할 수 있다.

5. LOB는 VARRAY에는 사용할 수 없다.

6. NCLOB은 OBJECT TYPE의 속성(attribute)으로 사용될 수 없으나, 메소드 정의를
하는 데는 NCLOB 파라미터를 사용할 수 있다.


Reference Documents
-------------------
Oracle8i Application Developer's Guide
Note:107441.1

출처 : Technical Bulletin

출처 : http://www.oracleclub.com/article/24469

[ DB Server의 dba group 아닌 다른 Unix 계정에 대해서 sqlplus 및 TNSNAMES.ORA 참조 환경 구성 ]

단계 1. DB Server의 dba group 아닌 Unix 계정에 대해서 sqlplus 등을 실행 권한 부여하기
단계 2. DB Server의 dba group 아닌 Unix 계정에 대해서 tnsnames.ora Lookup 권한 부여하기

단계 1) 실행 화일 권한 부여하기

예제    : Oracle DBMS 가 설치된 UNIX/LINUX 에 다른 계정(dba group)이 아닌 경우,
          $ORACLE_HOHE/bin 에 존재하는 화일(거이 여기 존재 sqlplus, tkprof, exp, imp) 에 권한 부여하기
         
Summary : Oracle Database 가 설치된 곳에서 Oracle_Home 아래의 Bin(실행화일 sqlplus 등) 에 대한 퍼미션이
          Oracle Patch 중 제한적으로 바뀌면 이것을 허용(덜 보안적으로) 하기 위해서는 아래 쉘을 실행 한다.
          $ cd $ORACLE_HOME/install  $ ./changePerm.sh

출처 : 메타링크
문서 번호 : 공지:438676.1
The script is located in the ORACLE_HOME/install directory on Unix and Linux systems (there is no equivalent for Windows).
This script ships with Oracle Database versions 9.2.0.8 and Oracle 10g releases.
Output when the changePerm.sh script is executed:

$ cd $ORACLE_HOME/install
$ ./changePerm.sh
-------------------------------------------------------------------------------
Disclaimer: The purpose of this script is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.
Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation. Running this script
should be done only after considering all security ramifications.
-------------------------------------------------------------------------------

Do you wish to continue (y/n) : y
Finished running the script successfully

Please see /tmp/changePerm_err.log for errors and /tmp/changePerm.log for the log of events

This script should be run by Oracle Software owner to relax the permission and can be run while database processes are active.

단계 2 ) TNSNAMES.ORA 공유하기
 
 UNIX 환경에서 ORACLE 과 다른 그룹에서 SQLPLUS / EXPORT / TNSPING 을 사용하기 위해서는
 changePerm.sh을  수행 하여 화일 Permission 을 부여 하고, PATH 를 지정하면 가능하다.
 그렇다면 이제 TNSNAMES.ORA 를 공유 하기 위해서는 어떻게 해야 할까?
 답은 TNS_ADMIN 을 통해서 가능하다.
 주의 사항 : tnsnames.ora 화일에 대한 퍼미션이 있어야 한다.
 tnsnames.ora 를 Lookup 할 필요가 있는 계정의 .profile 에 아래와 같이 설정한다.
 $ id
 testuser
 $ more .profile
 # Setting PATH for sqlplus / tnsping / exp / imp
 export ORACLE_BASE=/app/oracle
 export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
 export PATH=$ORACLE_HOME/bin:$PATH:
 # Setting PATH for tnsnames.ora
 export TNS_ADMIN=$ORACLE_HOME/network/admin