Topic: Database Administration >> How to change the nls_lang
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
| Title: How to change the nls_lang
|| Posted: Jun 12, 2007 04:08:56 AM
Total Post: 1
Joined: Jun, 2007
I have a database in oracle 10g, and it is recenty configured with AL32UTF8 as NLS_CHARACTERSET.
I need to change this to unicode, but can't seem to find any good way to do it. Anyone that know any good commands and a good way to procdeed?
Next, this might be abit strange question, but I come from the Microsoft world, and schemas dont seem to work the same in Oracle...
I need to set up a new schema for a new application, and then I need to grant 3 users to this schema. The schema should contain tables, procedures, packages and sequences.
|Posted: Jun 13, 2007 03:01:45 AM || |
Total Post: 166
Joined: Apr, 2007
This section explains the order in which NLS parameters are taken into account in the database client/server model. (This does NOT cover Thin JDBC connections)
There are 3 levels at which you can set NLS parameters: Database, Instance and
Session. If a parameter is defined at more than one level then the rules on which one takes precedence are quite straightforward:
1. NLS database settings are superseded by NLS instance settings
2. NLS database & NLS instance settings are superseded by NLS session settings
SELECT * from NLS_SESSION_PARAMETERS;
These are the settings used for the current SQL session.
These reflect (in this order):
1) The values of NLS parameters set by "ALTER SESSION "
ALTER SESSION set NLS_DATE_FORMAT = 'DD/MM/YYYY';
2) If there is no explicit "ALTER SESSION " statement done then it reflects the setting of the corresponding NLS parameter on the client derived from the NLS_LANG variable.
3) If NLS_LANG is specified with only the <Territory> part then AMERICAN is used as default <Language>.
So if you set NLS_LANG=_BELGIUM. WE8MSWIN1252 then you get this:
NLS_CURRENCY <euro sign here>
The difference between NLS_LANG=_BELGIUM.WE8MSWIN1252 (correct) and
NLS_LANG=BELGIUM.WE8MSWIN1252 (incorrect), you need to set the "_" as separator.
4) If NLS_LANG is specified with only the <Language> part then the <Territory> defaults to a setting based on <Language>.
So if you set NLS_LANG=ITALIAN_.WE8MSWIN1252 then you get this:
NLS_CURRENCY <euro sign here>
Note the difference between NLS_LANG=ITALIAN_.WE8MSWIN1252 (correct) and
NLS_LANG=ITALIAN.WE8MSWIN1252 (incorrect), you need to set the "_" as separator.
5) If NLS_LANG is specified without the <Language>_<Territory> part then the <Language>_<Territory> part defaults to AMERICAN_AMERICA.
So if you set NLS_LANG=.WE8MSWIN1252 then you get this:
The difference between NLS_LANG=.WE8MSWIN1252 (correct) and
NLS_LANG=WE8MSWIN1252 (incorrect), you need to set the "." as separator.
6) If the NLS_LANG is set (either like in point 3, 4 or 5) then parameters like
NLS_SORT, NLS_DATE_FORMAT, etc. can be set as a "standalone" setting and will overrule the defaults derived from NLS_LANG <Language>_<Territory> part.
So if you set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 and NLS_ISO_CURRENCY=FRANCE then you get this:
* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from
* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are not set then they are derived from NLS_TERRITORY
7) If the NLS_LANG is not set at all, then it defaults to
<Language>_<Territory>.US7ASCII and the values for the
<Language>_<Territory> part used are the ones found in
NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on the client side are ignored.
* If set, client parameters (NLS_SESSION_PARAMETERS) always take precedence over NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.
* This behavior cannot be disabled on/from the server, so a parameter set on the client always has precedence above an instance or database parameter.
* NLS_LANG cannot be changed by ALTER SESSION, NLS_LANGUAGE and NLS_TERRITORY can. However NLS_LANGUAGE and /or NLS_TERRITORY cannot be set as "standalone" parameters in the environment or registry on the client.
* NLS_SESSION_PARAMETERS is NOT visible for other sessions. If you need to trace this then you have to use a logon trigger to create your own logging table (based on session parameters)
* The <clients characterset> part of NLS_LANG is NOT shown in any system table or view.
* On Windows you have two possible options, normally the NLS_LANG is set in the registry, but it can also be set in the environment, however this is not often done and generally not recommended to do so. The value in the environment takes precedence over the value in the registry and is used for ALL Oracle_Homes on the server if defined as a system environment variable.
* NLS_LANGUAGE in the session parameters also declares the language for the client error messages.
* You cannot "set" a NLS parameter in an SQL script; you need to use ALTER SESSION.
Top of the Document
SELECT * from NLS_INSTANCE_PARAMETERS;
These are the settings in the init.ora of the database at the moment that the database was started or set through ALTER SYSTEM.
If the parameter is not explicitly set in the init.ora or defined by ALTER SYSTEM then its value is NOT derived from a "higher" parameter (we are talking about parameters like NLS_SORT that derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS, this is NOT the case for NLS_INSTANCE_PARAMETERS)
* NLS_LANG is not an init.ora parameter; NLS_LANGUAGE and NLS_TERRITORY are so you need to set NLS_LANGUAGE and NLS_TERRITORY separately.
* You cannot define the <clients characterset> or NLS_LANG in the init.ora
The client characterset is defined by the NLS_LANG on the client OS (see above).
* You cannot define the database characterset in the init.ora. The database characterset is defined by the "Create Database" command.
* These settings take precedence above the NLS_DATABASE_PARAMETERS.
* These values are used for the NLS_SESSION_PARAMETERS if the client the
NLS_LANG is NOT set.
* Oracle strongly recommends that you set the NLS_LANG on the client at least to
* The NLS_LANGUAGE in the instance parameters also declares the language for the server error messages in alert.log and in trace files.
Top of the Document
SELECT * from NLS_DATABASE_PARAMETERS;
Defaults to AMERICAN_AMERICA if there are no parameters explicitly set in the init.ora during database creation time. If there is parameters set in the init.ora during database creation you see them here. There is no way to change these after the database creation. Do NOT attempt to update system tables to bypass these settings! These settings are used to give the database a default if the INSTANCE and SESSION parameters are not set.
* NLS_LANG is not an init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.
So you need to set NLS_LANGUAGE and NLS_TERRITORY separately.
* These parameters are overridden by NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS.
* You cannot define the <clients character set> or NLS_LANG in the init.ora. The client character set is defined by the NLS_LANG on the client OS.
* You cannot define the database character set in the init.ora.
The database (national) character set NLS_(NCHAR)_CHARACTERSET) is defined by the "Create Database" command.
* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be overridden by instance or session parameters.
They are defined by the value specified in the "CREATE DATABASE command and are not intended to be changed afterwards dynamically. Do NOT update system tables to change the character set. This can corrupt your database and potentially make it impossible to open the database again.
* Setting the NLS_LANG during the creation of the database does not influence the NLS_DATABASE_PARAMETERS.
* The NLS_LANG set during the database creation has NO impact on the database National Characterset.
Additional SELECT statements:
A) SELECT name,value$ from sys.props$ where name like '%NLS%';
This gives the same info as NLS_DATABASE_PARAMETERS.
You should use NLS_DATABASE_PARAMETERS instead of props$.
Note the UPPERCASE '%NLS%'
B) SELECT * from v$nls_parameters;
This view shows the current session parameters and the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.
C) SELECT name,value from v$parameter where name like '%NLS%';
This view gives the same information as NLS_INSTANCE_PARAMETERS.
Note the LOWERCASE '%NLS%'
D) SELECT userenv ('language') from dual;
SELECT sys_context('userenv','language') from dual;
Both these SELECT statements give the session's <Language>_<territory> and the
DATABASE character set. The database character set is not the same as the character set of the NLS_LANG that you started this connection with! So don't be fooled, although the output of this query looks like the value of a NLS_LANG variable, it is NOT.
E) SELECT userenv ('lang') from dual;
This SELECT gives the short code that Oracle uses for the Language defined by NLS_LANGUAGE setting for this session. If NLS_LANGUAGE is set to French then this will return "F", if NLS_LANGUAGE is set to English then this will return "GB"
If NLS_LANGUAGE is set to American then this will return "US", and so on...
F) SHOW parameter NLS%
This will give the same as the NLS_INSTANCE_PARAMETERS
K P Ratnaker
|Posted: Jun 13, 2007 03:04:28 AM || |
Total Post: 112
Joined: Aug, 2006
alter system set nls_characterset='unicode'
or the type you required.
| Time Zone: EDT
|| Send this thread to your friend
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
Get FREE Magazines
|Top 10 Forum User|
| Murtuja Khokhar||857|
| Mohammed Taj||746|
| menon srivalsala kumar||639|
| positive fanatic||615|
| Gitesh Trivedi||580|
| Jayanta Sur||480|
| Vinoth Kumar||436|
| Vishant Sanghavi||409|
| Vigyan Kaushik||403|
| Gopu Gopi||367|