最早參考了中相關檔案

[@more@]

Oracle 10g 關於 NLS 的環境變數發生了一點變化:

For RDBMS 7.2.x version the variable is called ORA_NLS.
For version 7.3.x the variable is called ORA_NLS32.
For Oracle 8, 8i and 9 variable is called ORA_NLS33.
For Oracle 10g the variable is called ORA_NLS10.

ORA_NLS33 的設定值:

ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data

而 ORA_NLS10 只需要設定:

ORA_NLS10 = $ORACLE_HOME/nls/data

定義了 ORA_NLS10 則不需要單獨定義 ORA_NLS33 等引數了。

目前應該仍然是向後相容 ORA_NLS33 的。

ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.
	Doc ID:	Note:77442.1	Type:	BULLETIN
	Last Revision Date:	22-JUN-2006	Status:	PUBLISHED

PURPOSE

The purpose of this document is to provide summarized information on when
environment variable ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) needs to be set.

SCOPE & APPLICATION

Database administrators - Oracle Support Analysts

=========================================================================

For RDBMS 7.2.x version the variable is called ORA_NLS.
For version 7.3.x the variable is called ORA_NLS32.
For Oracle 8, 8i and 9 variable is called ORA_NLS33.
For Oracle 10g the variable is called ORA_NLS10.

Note:
In this document, ORA_NLSxx term should be generic for all versions.

Please be aware that your ORACLE_HOME variable should also be set correctly

A) On UNIX we recommend to set ORA_NLSxx always in the User enviroment of the
ORACLE user.

The variable is used to locate the NLS libraries when:

1. If you create a database with any character set other than US7ASCII
on Oracle version 7.x and up.

If you are using version 7 then you must set the environment variable ORA_NLS32 to:

ORA_NLS32 = $ORACLE_HOME/ocommon/nls/admin/data

If you are using a Oracle8, 8i or 9 version set the ORA_NLS33 environment variable to:

ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data

If you are using a Oracle10G version set the ORA_NLS10 environment variable to:

ORA_NLS10 = $ORACLE_HOME/nls/data

There is no need to define ORA_NLS32 or ORA_NLS when using oracle 8 or higher.
There is no need to define ORA_NLS33, ORA_NLS32 or ORA_NLS when using oracle 10G.

When you try to create a database without setting ORA_NLSxx parameter you will
have the error:

ORA-12701: "CREATE DATABASE character set is not known"

If you are creating database upon installation of Oracle software,
you need to add ORA_NLSxx in the startup file of the oracle software owner.

2. When database is already created and SQL Net (or Net8) connections need
to be established, ORA_NLSxx is used by the listener and client software
to define date/time format masks and perform characterset conversions.
So make sure that in the client's and (!) listener environment the ORA_NLSxx also is set:

Please note that we don't advise to use a version 9 listener for a version 8 database
or a version 10 listener for a version 9 or 8 database seen there where many changes
to the NLS layers between those versions.

If you are using a SQL*PLUS or listener Oracle7 version, set the environment variable:

ORA_NLS32 = $ORACLE_HOME/ocommon/nls/admin/data

If you are using a SQL*PLUS or listener Oracle8, 8i or 9 version, set the environment variable:

ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data

If you are using a SQL*PLUS or listener Oracle10G version, set the environment variable:

ORA_NLS10 = $ORACLE_HOME/nls/data

There is no need to define ORA_NLS32 or ORA_NLS when using oracle 8 or higher.
There is no need to define ORA_NLS33, ORA_NLS32 or ORA_NLS when using oracle 10G.

Note:
-----
* When establishing connection through SQL*Net, connecting to an Oracle8
database with a Unicode UTF8 character set, make sure you are using
SQL*Net version 2.3.4 and higher. Otherwise you will get an error, as
SQL*Net V2.3.4 and higher is required to connect to a UTF8 database.
(i.e. ORA-3106)
* In some circumstances you will see that Oracle recommends to set
ORA_NLS10 to $ORACLE_HOME/nls/data/9idata instead of $ORACLE_HOME/nls/data
The why is explained in Note 292942.1 Language and Territory definitions
changed in 10g vs 9i and lower

Please note that unless you have a good reason, we strongly recommend to have
ORA_NLS10 set to $ORACLE_HOME/nls/data.
Using $ORACLE_HOME/nls/data/9idata must be considerd as a temporary workaround.

3. For characterset conversion and date/time format support:

set the ORA_NLS32 environment variable for the release 7.3.x environment

set the ORA_NLS33 environment variable for the version 8,8i and 9 environment

set the ORA_NLS10 environment variable for the version 10G environment

Verify that the client has the correct NLS environment variables set (like NLS_LANG).
For more info on that subject please see:

Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 241047.1 The Priority of NLS Parameters Explained.

An error is generated when release 7.3 NLS code tries to load a version 8
character set.

There is no need to define ORA_NLS32 or ORA_NLS when using oracle 8 or higher.
There is no need to define ORA_NLS33, ORA_NLS32 or ORA_NLS when using oracle 10G.

4. On WINDOWS platforms there is normally no need to define ORA_NLSxx.

If you want however you can define this in the correct HOMEx entry in the registry,

HKEY_LOCAL_MACHINESOFTWAREORACLE for version 7
or
HKEY_LOCAL_MACHINESOFTWAREORACLEHOMEx for version 8 and 9
or
HKEY_LOCAL_MACHINESOFTWAREKEY_ for version 10

The default directory for windows is

for 8.0: ORA_NLS33=%ORACLE_HOME%NLSRTL33DATA

for 8i and 9i: ORA_NLS33=%ORACLE_HOME%ocommonnlsADMINDATA

for 10g: ORA_NLS10=%ORACLE_HOME%nlsDATA

See Note 73963.1 Using multiple ORACLE HOMES on Windows platform

Please do NOT define ORA_NLSxx as (system) ENVIROMENT variable on windows.
Please do NOT define ORACLE_HOME and/or ORACLE_BASE as (system) ENVIROMENT
variable on windows as this will invalidate the working of ORA_NLSxx.

5. When Developer is installed.

Note1:
ORA_NLSxx does not need to be set for Developer 6.0.

For Oracle version 8.x, if we connect from the client and use Developer
version 1.6.1 then ORA_NLS33 needs to be set:

$ORACLE_HOME/ocommon/nls/admin/datad2k

For other versions it should point to:

$ORACLE_HOME/ocommon/nls/admin

Note2:
$ORACLE_HOME/ocommon/nls/admin/data2k subdirectory is created by
Developer version 1.6.1 only. This is caused by the fact that this
version of Developer uses two versions of NLSRTL library at once: version 3.2
for Oracle common RDBMS libraries (known as RSF on Windows) and version 3.3
for Forms processing. As these two NLSRTL versions needs different *.nlb files,
two data directories are created: 'data' for 3.2 and 'datad2k' for 3.3.

NLSRTL 3.2 files are pointed to by the ORA_NLS32 variable, which defaults from
ORACLE_HOME. NLSRTL 3.3 files are pointed by the ORA_NLS33 variable,
which must be set explicitly to $ORACLE_HOME/ocommon/nls/admin/datad2k.

Note3:
When exporting from the Developer 1.6.1 environment, you might be getting a
ORA-600 [23] [] error. If this is the case, try setting ORA_NLS33 to

$ORACLE_HOME/ocommon/nls/admin/data

instead of

$ORACLE_HOME/ocommon/nls/admin/datad2k

This should resolve the internal ORA-600 [23][].

6. If ORA_NLSxx is not set or can't be read and the database is started with
other languages and character sets, the database will default and character
set will not be recognized.

This can result in:

a. Character set translation not occurring for clients using a character set
different from the database's. This may result in corrupt data in certain
applications as 8-bit or 16-bit or 32-bit may be lost and misinterpreted.

b. ORA-12705 (may be combined with ORA-604 errors).

c. "SQL*Loader-266: Unable to locate character set" error.

d. Messages being displayed in a language different from that expected.

e. ORA-3106 fatal two-task communication protocol error. ORA-600[12333] which
will have the same meaning as ORA-3106 in this case. At the client you may
see a TNS-12571 or ORA-12571.

You can check which NLS setting are valid for this platform by connecting to
the database as a DBA and issuing the following command:

SELECT * FROM V$NLS_VALID_VALUES;

If this only returns US7ASCII then ORA_NLSxx is INCORRECTLY set.

To find out what character set you have, issue the following command connected
as sys, system or DBA:

SELECT * FROM NLS_DATABASE_PARAMETERS;

Look for the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET (from version 8 onwards) parameter.

you may also want to check that language files were installed
properly on your system. To do that go to directory:

$ORACLE_HOME/ocommon/nls/admin/data

or from 10g onwards:

$ORACLE_HOME/nls/data

and issue the following command:

strings * | grep -i we8mswin1252

where we8mswin1252 (or any other than us7ascii) is the character set
choosen for the database.

Solution:
---------
set ORA_NLSxx correct and restart the instance (also the service on windows).

7. If ORA_NLSxx is not / wrongly set or can't be read and the listener is started the you will
typically see that a connection with NLS_LANG *not* set or set to AMERICAN_AMERICA.US7ASCII
works. But a connection with other territorry and language like DUTCH_BELGIAN.WE8MSWIN1252
fails with ORA-12705 that is combined with ORA-604. You will see this also when using
a 817 listener for a 9i database.

A ORA-12705 combined with ORA-604 always points to a server problem, database and/or listener started
with incorrect ORA_NLSxx definition or OS permission problems.

Solution:
---------
set ORA_NLSxx correctly and restart the listener (also the service on windows).
and check file/directory permissions

We recommend to use a 8i listener for a 8i db, a 9i listener for a 9i db etc...

If you really want to use a 9i listener for a 8i instance then define the correct
ORA_NLS33 in the listener.ora like this:

(SID_DESC =
(GLOBAL_DBNAME = o8i.oracle.com)
(ORACLE_HOME = /disk1/oracle8i)

(ENVS='ORACLE_HOME=/disk1/oracle8i,ORA_NLS33=%ORACLE_HOME%/ocommon/nls/admin/data,LD_LIBRARY_PATH=/disk1/oracle8i/lib')
(SID_NAME = o8i)
)

Make sure you disable "automatic listener registration" for the 8i database
if you use 1521 as listener port.

8i init.ora
local_listener="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname) (PORT=1599)))"

where 1599 is a port number NOT used by the 9i listener, so that the 8i db is NOT
able to find the 9i listener.(this has no performance impact)

Note 130574.1 Disabling Automatic Registration of the Database with the Default Listener

However then do not forget to start the 9i listener with the 9i settings
ORACLE_HOME=/disk1/oracle9i
ORA_NLS33=%ORACLE_HOME%/ocommon/nls/admin/data

8. If you have only a ORA-12705 alone then the problem is purely on the client side.
You have a incorrect defined ORA_NLSxx or you have specified a incorrect NLS
variable on the client side, like NLS_LANG=WE8IOS8859P1 (NLS_LANG=.WE8ISO8859P1 is correct)
-> see Note 241047.1 The Priority of NLS Parameters Explained.

Solution:
---------

Unix: make sure you use the correct syntax for env variables, remove them for testing if needed
we DO recommend to set always ORA_NLSxx, ORACLE_HOME and NLS_LANG correctly in a unix shell env.

Windows: check the registry key of the oracle_home you are using. Also check for variables
set in the environment (type "set" in a CMD prompt).
We do NOT recommend to set Oracle variables in the environment on windows

Generic: check file/directory permissions

9. OI_NLS32 is a parameter used by the Oracle Universal Installer and should NOT be alterd

See also:
Note 1058400.6 ORA-12701 When Creating a Database

Note 241047.1 The Priority of NLS Parameters Explained.

Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
1.2 What is this NLS_LANG thing anyway?
4.2 How can I Check the Client's NLS_LANG Setting?
Note 179133.1 The correct NLS_LANG in a Windows Environment
Note 264157.1How to set correctly NLS_LANG on Unix Environments
Note 265090.1 How to check Unix terminal Environments for the capability to display extended characters.
Note 229786.1 NLS_LANG and webservers explained.

Note 62107.1 The National Character Set in Oracle8
Note 276914.1 The National Character Set in Oracle 9i and 10g

For further NLS / Globalization information you may start here:
Note 267942.1 Globalization Technology (NLS) Knowledge Browser .

Oracle 10g NLS 環境變數設定/和以前版本有變化

相關文章