Oracle Tips : How to change database character set

by cryst · Published March 17, 2011 · Updated March 1, 2011

Oracle Tips : How to change database character set

This article gives a overview of methods to change the database character set .

1. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’;

Parameter Value
NLS_CHARACTERSET AL32UTF8

If you want to change NLS_CHARACTERSET from AL32UTF8 to TH8TISASCII

2. Connect to user SYS

3. Update value

UPDATE PROPS$
SET VALUE$ = ‘TH8TISASCII’
WHERE NAME = ‘NLS_CHARACTERSET’;

COMMIT;

4. Restart database

SHUTDOWN IMMEDIATE;

STARTUP;

5. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT * FROM
V$NLS_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’;

Parameter Value
NLS_CHARACTERSET TH8TISASCII