How to check Globalization Supports for Oracle Database on UIM server
Article ID: 131441
DX Infrastructure ManagementNIMSOFT PROBES
This kb article describes how to check Oracle Globalization Supports on UIM server
Release: Component: UIMUMP
-- To check the NLS settings.
Run below query in a sqlplus session on UIM server
COL "PARAMETER" format a20 COL "SESSION" format a15 COL "DATABASE" format a15 COL "INSTANCE" format a15 SELECT * FROM (SELECT 'SESSION' SCOPE,nsp.* FROM nls_session_parameters nsp UNION SELECT 'DATABASE' SCOPE,ndp.* FROM nls_database_parameters ndp UNION SELECT 'INSTANCE' SCOPE,nip.* FROM nls_instance_parameters nip ) a PIVOT (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE) FOR SCOPE IN ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));
This returns the NLS parameter values at the session, instance and DB level. Some of the UIM components may modify NLS parameter values using a logon trigger but won't modify all the values.
The NLS_CHARACTER_SET value at the Database level is the default DB character set specified when creating the database and this cannot be overridden by any means in a client session.
UIM components should not have an assumption on the character set used. However, after installing Oracle client and before installing UIM, make sure to specify a compatible character set that allows for conversion like ZHS16GBK (Simplified Chinese) with AL32UTF8 if server and client are in different character sets. Make the same language on both ends makes Oracle transfer and receive data blindly without any conversion.
-- To determine the character set.
The NLS_LANG environment variable controls default date, monetary, and numeric format and determines the language used for Oracle messages, sorting, day names, and month names if these are not set in the session. It also specifies the character set used by the client application so that Oracle can do the proper conversion.
1. Check existing NLS_LANG setting
Check NLS_LANG on the Linux platform:
SQL> HOST ECHO $NLS_LANG
On the Windows platform:
SQL> HOST ECHO %NLS_LANG%
It is possible that NLS_LANG is set in Windows registry by Client installer according to types of installation:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name> for Oracle Client installation
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE for Oracle Instant Client installation
If the result contains %NLS_LANG%, this indicates that NLS_LANG is not set on the UIM server. This is possible like for example Oracle instant client basic is just unzipped.
2. If otherwise NLS_LANG not set
The character set is US7ASCII.
3. The NLS_LANG setting should reflect the code page/locale of the client
This should be done by the Oracle Client Installer, for example, character set 'WE8MSWIN1252' is for locale uses Western European language, character set 'ZHS16GBK ' is for Simplified Chinese.
Windows code pages can be found in the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage. Default code page can be found in value 'ACP' and 'OEMCP', which are determined by System Locale.
Locale information of a Unix/Linux server can be found using:
The current local on the server can be found:
If you plan to set a non-default character set, consult local DBA first.