Oracle NLS_PARAMETERS And Database CHARACTER SET

Contents of this Post

1. Query/Select All NLS Parameter
2. Change NLS_TERRITORY, NLS_LANGUAGE, NLS_CURRENCY, NLS_ISO_CURRENCY
3. Change Database CHARACTER SET

Find All CHARACTER SET,TERRITORY,LANGUAGE

SQL> select * from V$NLS_VALID_VALUES;
SQL> select * from V$NLS_VALID_VALUES where ISDEPRECATED='TRUE';
OR
SQL> select * from x$ksulv;


SQL> SHOW PARAMETER NLS_
OR
SQL> select * from v$nls_parameters;  (Dynamic View)
OR
SQL> select * from NLS_DATABASE_PARAMETERS;  (Static View)


Note :-

The NLS_DATABASE_PARAMETERS view shows the NLS settings for the database itself.These represent the default values that will govern the instance, unless they are overridden by initialization parameter settings.

So How to change NLS_DATABASE_PARAMETERS view ::- Take backup of your database , Reinstall it with right character set then restore the backup.

The Dynamic DDV is v$nls_parameters which reflects the below changes immediately. Also the "SQL> SHOW PARAMETER NLS_" will display the changed value immediately.

Note that the NLS_CURRENCY may not reflect as expected.
To print the correct CURRENCY value you need to set the below in UNIX/Windows Environment

export NLS_LANG="ENGLISH_UNITED KINGDOM.AL32UTF8"  (In UNIX) (For UK)

Set NLS_LANG In Windows ::- The correct NLS_LANG in a Microsoft Windows Environment (Doc ID 179133.1)

Now You can see the NLS_CURRENCY = £ (Pound Sign) of UNITED KINGDOM.


£ "POUND" Symbole  Can be create using "Alt + 156 (Num Pad)"



Change NLS_TERRITORY, NLS_LANGUAGE, NLS_CURRENCY, NLS_ISO_CURRENCY

Method I
Manually Change the Parameter

Backup current spfile and pfile in $ORACLE_HOME/dbs

SQL> Shut Immediate

Add below lines to pfile

*.NLS_TERRITORY ='UNITED KINGDOM'
*.NLS_LANGUAGE='ENGLISH'
*.NLS_ISO_CURRENCY='UNITED KINGDOM'
*.NLS_CURRENCY='#'

Start the DB with pfile

SQL> create spfile from pfile;

SQL> Shut immediate
SQL> Startup



Method II
OR Directly change in SPFILE and bounce the database.

ALTER SYSTEM SET NLS_TERRITORY ='UNITED KINGDOM' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_LANGUAGE='ENGLISH' SCOPE=SPFILE;;
ALTER SYSTEM SET NLS_ISO_CURRENCY='UNITED KINGDOM' SCOPE=SPFILE;;
ALTER SYSTEM SET NLS_CURRENCY='#' SCOPE=SPFILE;

Method III
OR Use a LOGON TRIGGER to set those Parameters in Session level.

CREATE OR REPLACE TRIGGER sys.schema_nls_session_settings AFTER LOGON ON BOB.SCHEMA
BEGIN
execute immediate 'alter session set NLS_LANGUAGE=''ENGLISH''';
execute immediate 'alter session set NLS_TERRITORY=''UNITED KINGDOM''';
execute immediate 'alter session set NLS_CURRENCY=''£''';
execute immediate 'alter session set NLS_ISO_CURRENCY=''UNITED KINGDOM''';
END;
/



Change Database CHARACTER SET

SQL> SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = '%CHARACTERSET';
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET';

PARAMETER                                   VALUE
-------------------------------------- --------------------------------
NLS_CHARACTERSET                   WE8ISO8859P1
NLS_NCHAR_CHARACTERSET   AL16UTF16


As a normal practice we are doing like below.

SQL> alter database CHARACTER SET AL32UTF8;
alter database CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Memorize the value for below parameters

SQL> show parameter CLUSTER_DATABASE
SQL> sho parameter job_queue_processes
SQL> sho parameter AQ_TM_PROCESSES


 $ sqlplus / as sysdba

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;  -- For RAC Node Only

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

SQL> SHU IMMEDIATE

SQL> STARTUP

SQL> SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET';

VALUE
------------------
AL32UTF8


Change NLS_NCHAR_CHARACTERSET

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;  -- For RAC Node Only

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP



Reset The Parameter Settings

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;  -- For RAC Node Only
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=100;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

Note :-

If you are facing any issue in application after above CHARACTER SET change then go for below document and/or contact oracle support.

Character Set Migration using CSSCAN and CSALTER (Doc ID 225912.1)