Doc ID:
|Doc ID: |Content Type: |
|Note:276914.1 |TEXT/PLAIN |
| | |
|Subject: |Creation Date: |
|The National Character Set in Oracle 9i and 10g |22-JUN-2004 |
| | |
|Type: |Last Revision Date: |
|BULLETIN |30-NOV-2004 |
| | |
|Status: | |
|PUBLISHED | |
| | |
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 or 10g?
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 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 in a Nchar or Nvarchar2 col but it comes back as ? or � ...
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 ?
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.
See [NOTE:260893.1] Unicode character sets in the Oracle database
for more info about the difference between them.
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 prepending 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 or 10g?
---------------------------------------------------------------
* 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 two 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 environment parameter correctly
to the 9i Oracle_Home during the upgrade.
[NOTE:77442.1] ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.
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...
6) Can I change the AL16UTF16 to UTF8 / I hear that there are problems with AL16UTF16.
--------------------------------------------------------------------------------------
a) 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 advice the keep
the default AL16UTF16 NLS_NCHAR_CHARACTERSET.
b) 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.
C) You can change from AL16UTF16 to UTF8 (or inverse) following these steps:
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;
@RDBMS/ADMIN/N_SWITCH.SQL
SHUTDOWN IMMEDIATE;
3. Restore the parallel_server parameter in INIT.ORA, if necessary.
4. STARTUP;
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 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 you had as NLS_NCHAR_CHARACTERSET in 8(i).
If you are not using N-types then keep the default AL16UTF16 or use UTF8,
it doesn't matter if you don't use the 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).
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.
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 influance 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 in a Nchar or Nvarchar2 col but it comes back as ? or � ...
--------------------------------------------------------------------------------------------------
see point 13 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 ?
-------------------------------------------------------------------------------------
The only possible advantage is storage (disk space).
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 i can be advantageous to use N-types for those columns.
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
But note:
* This is not true for (N)CLOB, they are both encoded a internal fixed-width Unicode character set
[NOTE:258114.1] Possible action for CLOB/NCLOB storage after 10g upgrade
so they will use the same amount of disk space.
* 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.
* 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 VARCHAR (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.
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 Character Conversion Work?)
[NOTE:159657.1] Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.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:150091.1] Globalization Technology (NLS) Library index
[NOTE:60134.1] Globalization (NLS) - Frequently Asked
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.