Change Oracle Database Character Set : NLS_CHARACTERSET

Change Oracle Database Character Set : NLS_CHARACTERSET

Step 1 – About

NLS_LANG is an environment parameter used to set the locale of a client application.

Setting the NLS_LANG environment parameter is the simplest way to specify locale behaviour for Oracle Database software. It sets the language and territory used by the client application. It also sets the client’s character set, which is the character set for data entered or displayed by a client program such as SQL*Plus.

NLS_LANG is not a NLS parameter, it’s an oracle environment variable set:

  • As an environment variable on UNIX platforms.
  • As a key in the registry on Windows platforms.

Step 2 – Syntax to connect

sqlplus sys as sysdb
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit 
Production With the Partitioning, OLAP and Data Mining options

Current database character set is AL32UTF8.
Connected to Oracle Database

Step 3 – Syntax to change Oracle DB Character Set

-- Check the NLS parameters first.
set linesize 2000;
select * from NLS_DATABASE_PARAMETERS; 


-- The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:
-- Shut down the database
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;

-- If you want to change from AL32UTF8 to WE8MSWIN1252. 

ALTER DATABASE CHARACTER SET INTERNAL_USE  WE8MSWIN1252;
alter database national character set internal_use AL16UTF16;

SHUTDOWN IMMEDIATE; 
STARTUP;

Leave a Reply

Your email address will not be published. Required fields are marked *